Default constraint in Sql Server

Hi friends! In our last session we learned how to create tables (tblPerson and tblGender) and we made a relation between them using foreign key constraints.

This is the structure of the previous tables that we have worked on.

In this post, we will be learning how to add a Default Constraint into a table in SQL Server.

A column default value can be specified using Default constraint. The Default Constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified, including NULL, i.e if we explicitly provide NULL for any column then Default Constraint will not work.

Here is the General Syntax for Altering an existing column to add a default constraint:

ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME }

Here is the Syntax for Adding a new column, with default value, to an existing table:

ALTER TABLE { TABLE_NAME } 
ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL } 
CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }

This command will add a default constraint, DF_tblPerson_GenderId.

ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 1 FOR GenderId

Now, let’s test our query.
This insert statement below does not provide a value for GenderId column, so the Default Constraint should work and set its default value of 1 will be inserted in this record.

Insert into tblPerson(ID,Name,Email) values(4,'XYZ','x@x.com')

Now Select your rows from the table using this statement

Select * from tblPerson

Now, you could see that the Default Constraint has inserted a default value of in the table.
Let’s try by passing NULL value. The following Insert Statement will insert NULL, instead of using the default.

Insert into tblPerson(ID,Name,Email,GenderId) values (5,'ABC','a@a.com',NULL)

Finally, If you want to drop any constraint from your table, use this statement.

ALTER TABLE { TABLE_NAME } 
DROP CONSTRAINT { CONSTRAINT_NAME }

Summary:
So, Guys this is all about Default constraint in Sql Server
I Hope in this post covered all the points about how to pass a default value into the table if any value is not specified for that, which will be helpful to understand the concept Default constraint

Please share this post with your friends and colleagues.
For any queries please post a comment below.

Happy Coding 😉