Default Constraint in SQL

sqlAs we have seen in previous tutorial that we can have non-nullable columns in SQL by using NOT NULL constraint.  But if we would not use NOT NULL constraint and insert values in all other columns except to a nullable column, then that column will have null value by default in it.  To overcome this issue, we can add DEFAULT constraint to it.  This constraint will insert default value to that specific nullable column instead of null.

This DEFAULT constraint can be added to an existing column in a table or a new column can be added to the table with DEFAULT constraint.  Syntax for both scenarios is given below.

Existing Column:

ALTER TABLE {Table_Name}
ADD CONSTRAINT {Constraint_Name}
DEFAULT {Default_Value} FOR {Column_Name}

New Column:
ALTER TABLE {Table_Name}
ADD {Column_Name} {Data_Type} {NULL or NOT NULL }
CONSTRAINT {Constraint_Name} DEFAULT {Default_Value}


NOTE:  If you are explicitly passing a null value to a nullable column which contains DEFAULT constraint, then null value will be inserted instead of default value.  To avoid such issue, you must make your column non-nullable.