Search code examples
sqlconstraintscreate-table

Naming for contraints when creating a table

I see following SQL in a system:

create table Account
(
    [AccountId] int not null,
    [EquityStatus] int not null constraint DF_Account_EquityStatus default(1),
    [DerivativeStatus] int not null constraint DF_Account_DerivativeStatus default(1),
    [HasSecurityAgreement] tinyint not null constraint DF_Account_HasSecurityAgreement default(0),
    ...
)

Why it names every contraints for almost every column? Is there any benefit to do this?

Solution

  • You can let your system assign a system-generated name for your constraints but then it becomes very difficult, from a maintenance perspective, if you want to alter or drop a constraint later - you generally have to perform such operations by name and if you let the system auto-generate the name, you won't know the correct name to use.

    In addition, if scripts are your primary means of deployment and you have multiple environments (e.g. development, staging, test, etc) then the system generated names will differ in each environment which makes it far more difficult if you want to compare two environments to establish current differences.