Creating a default named constraint with MS SQL Server

This is something that I never previously thought of until I went and tried to delete an index that was created and it had a random name across different environments. To say the least it was a real pain and ended up being where more manual than I ever thought it could be.

This is when a learned how to create default constraints with a defined name so it would no longer generate a random index name.

Let’s start by looking at an example of creating a default constraint when adding a new field to a table that creates a random name:

[code]
ALTER TABLE Account
ADD IsLive BIT NOT NULL DEFAULT(1)
[/code]

The idea behind this example is to add a new BIT field to a table called account and I want it to be NOT NULL with a default value of true aka 1.

When SQL creates this it picks a random string after the table name and new field, e.g.: DF__Account__IsLive__5418334F

If you were to run this on your database your 5418334F would most likely not match that random string.

To specify a more consistent name a small alteration is required:

[code]
ALTER TABLE Account
ADD IsLive BIT NOT NULL CONSTRAINT DF_Account_IsLive DEFAULT(1)
[/code]

Now when I look at the constraints on the Account table it will be called: DF_Account_IsLive thus allowing me to create a consistent naming convention.