Follow @endyourif rss Facebook LinkedIn

Creating a default named constraint with MS SQL Server Creating a default named constraint with MS SQL Server

Published on Jun 1, 2022

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:




ALTER TABLE Account
	ADD IsLive BIT NOT NULL DEFAULT(1)


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:


ALTER TABLE Account
	ADD IsLive BIT NOT NULL CONSTRAINT DF_Account_IsLive DEFAULT(1)


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.

Tags: SQL | constraint | mssql

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP