SQL Server Default Constraint: A Guide for Beginners

In Microsoft SQL Server, one of the things we should always keep in mind is the integrity of our data. One of the best ways to maintain data integrity is to introduce constraints into our tables.

Constraints are a great way to restrict the data that can be inserted into our tables. Constraints help to create a situation where invalid data cannot be inserted into our tables.

One of the most helpful constraints you can create in SQL Server is called a default constraint.

In this very brief tutorial, we’re going to discuss everything you need to know about default constraints and how they can help preserve the integrity of our data.

Default constraints are outlined in the following FREE Ebook:

FREE Ebook on SQL Server Constraints!

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

• Primary Key constraints
• Foreign Key constraints
• Default constraints
• Check constraints
• Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

We’ll discuss the following topics in this tutorial about default constraints:

1. What is a default constraint?
2. Examples of creating and using a default constraint
3. Tips and tricks

And so it begins…

1. What is a default constraint?

A default constraint is placed on a column in a table, and outlines a default value to be used in an INSERT statement when no value is provided for that column.

When inserting rows into a table, it is possible to simply not outline a column value. Let’s look at the following table:

CREATE TABLE PetInfo
(
PetID INT IDENTITY,
PetName VARCHAR(20),
OwnerFirstName VARCHAR(20),
OwnerLastName VARCHAR(20)
)

We work at a Veterinarian hospital, and we use this table to track the pets we treat and their owners.

Since I didn’t outline the nullability of my columns (bad practice), they all defaulted to allow NULL.

So let’s say we wanted to insert a new row into this table for a pet named Barnabus. We know the pet’s name of course, and we know the owners first name. But maybe we don’t know their last name right now, and that’s ok. We still just want to go ahead and get them in the database. We can update the row later to include the owner’s last name.

The INSERT statement would look like this:

INSERT INTO PetInfo (PetName, OwnerFirstName)
VALUES ('Barnabus', 'Amy')

We chose to explicitly exclude the OwnerLastName value, and since that column is nullable, the value in that column becomes NULL:

Maybe that’s ok, but maybe we’d rather have something other than NULL get inserted into that column. I mean, the person definitely has a last name, right? We just don’t know what it is!

NULL is a bit ugly. Kinda like whatever the f*ck this critter is:

Through the use a default constraint, we can default a value of our choosing when we run an INSERT statement without a column value.

2. Examples of creating and using a default constraint

There are a few ways we can add a default constraint to a column in a table. We’ll go over each.

Adding a default constraint in an ALTER TABLE statement:

If you want to add a default constraint to a table that already exists (and maybe already has data within it), you can run an ALTER TABLE statement to easily add the constraint.

You would use the ADD CONSTRAINT keywords, like this:

ALTER TABLE PetInfo ADD CONSTRAINT dflt_LastName DEFAULT('unknown') FOR OwnerLastName

Let’s break down the syntax:

1. Use the “ALTER TABLE” keywords to outline the table you want to add the constraint to.
2. Use the “ADD CONSTRAINT” keywords, followed by the name you want to give the constraint.
3. The keyword “DEFAULT” outlines that this will be a default constraint.
4. Next to the keyword “DEFAULT“, outline what you want the default value to be in parentheses
5. Finally, you need to outline which column the constraint will be on. Use the “FOR” keyword followed by the name of the column.

That’s it!

Now, if we add another row that doesn’t have a OwnerLastName value, we’ll see that the default constraint will plug in the word ‘unknown‘ for us:

Superb!

The great thing about a default constraint is that the default value can be anything you want it to be (as long as the data type matches).

I chose to use the word ‘unknown’ as my default value, but I could’ve used any 20-digit-or-less character string I wanted, such as:

• ‘???’
• ‘-‘
• ‘N/A’ 
• ‘Spaghetti’

Whatever!

Adding a default constraint in a CREATE TABLE statement:

Let’s drop the PetInfo table to demonstrate how you could create your default constraint from the start:

DROP TABLE PetInfo

If we wanted to create a default constraint on the OwnerLastName column from within a CREATE TABLE statement, the syntax would look like this:

CREATE TABLE PetInfo
(
PetID INT IDENTITY,
PetName VARCHAR(20),
OwnerFirstName VARCHAR(20),
OwnerLastName VARCHAR(20) CONSTRAINT dflt_LastName DEFAULT('???')
)

The syntax is simple:

1. Outline the keyword “CONSTRAINT” next to the column you want to add the default constraint to.
2. Outline the name you want to give your constraint. I chose to name it dflt_LastName
3. Use the keyword “DEFAULT” to outline that this will be a default constraint
4. In parentheses, outline what you want the default value to be. In our example, the default value is three question marks.

That’s it! Again, if we insert a row without a value for OwnerLastName, the default constraint will plug in ‘???‘ for us:

Superb!

Simple way to outline a default constraint in a CREATE TABLE statement:

Let’s drop the PetInfo table again to demonstrate the final method for adding a default constraint to a table:

DROP TABLE PetInfo

In our CREATE TABLE statement, we can create a default constraint on a column by simply outlining the word “DEFAULT” next to the column, followed by the default value we want to use. Like this:

CREATE TABLE PetInfo
(
PetID INT IDENTITY,
PetName VARCHAR(20),
OwnerFirstName VARCHAR(20),
OwnerLastName VARCHAR(20) DEFAULT('-')
)

If the default constraint is created in this way, SQL Server will generate the name of the constraint for you. The name won’t be as nice as a name you could come up with. For example, here is what my SQL Server came up with when I ran that CREATE TABLE statement:

If you’re okay with a name like that, then that’s fine!

3. Tips and tricks

Here is a list of some helpful tips and tricks you should know when working with default constraints in SQL Server:

Tip # 1: You can use the DEFAULT keyword in an INSERT statement

In an INSERT statement, it’s possible to explicitly outline that you want to use the default constraint to populate a column. You would simply outline the keyword “DEFAULT”

So again, if we have a default constraint on the OwnerLastName column, we can do an INSERT statement like this:

Pretty cool, eh?

Tip # 2: You can add a default constraint to a non-nullable column, too

In my examples, we were focused on nullable columns (columns that allow NULL). You could also add a default constraint to a column that is non-nullable (columns that disallow NULL).

I just didn’t want you to think the concept of a default constraint only applies to nullable columns. It can be applied to any column!

Tip # 3: Drop a constraint using the DROP CONSTRAINT statement

Dropping a constraint is simple. Just run the DROP CONSTRAINT statement in an ALTER TABLE statement, like this:

ALTER TABLE PetInfo DROP CONSTRAINT dflt_LastName

Next Steps:

Leave a comment if you found this tutorial helpful!

If you found this tutorial helpful, make sure you download your FREE Ebook:

FREE Ebook on SQL Server Constraints!

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

• Primary Key constraints
• Foreign Key constraints
• Default constraints
• Check constraints
• Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!