Creating a default named constraint with MS SQL Server 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: 



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.

Published on Jun 1, 2022

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | constraint

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.

Jamie Munro
Jamie Munro

Jamie Munro is the author of ASP.NET MVC 5 with Bootstrap and Knockout.js, Knockout.js: Building Dynamic Client-Side Web Applications, 20 Recipes for Programming MVC 3, and 20 Recipes for Programming PhoneGap. He has been developing websites and web applications for over 20 years.

Jamie began his writing career in 2009. As the success of Jamie's blog grew, he turned his writing passion to books about web development in hopes that his many years of experience could be passed on to his readers.

Tutorials

Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.

Front-End

Server-Side

Database