Very frequently Jr. Developers request script for creating default constraint over table column. I have written following small script for creating default constraint. I think this will be useful to many other developers who want this script to keep handy.
ALTER TABLE Customers CONSTRAINT DF_Customers_Address2 DEFAULT 'UNKNOWN' FOR Address2
Remember currently I am using a default value as unknown as my column datatype is varchar. However, if the column is of datatype integer you have to specify the default value as an integer. Remember here we are specifying only default constraints so if the column already has a value the column will not be filled with the default values.
If the column is not nullable, it must either contain a value or will need a default constraint with a value, otherwise, the insert statement will fail. Here are few additional blog posts on a similar subject:
- SQL SERVER – Add Column With Default Column Constraint to Table
- SQL SERVER – Add New Column With Default Value
- How Default Value and Nullable Column Works? – Interview Question of the Week #129
- SQL SERVER – How to Disable and Enable All Constraint for Table and Database
- SQL SERVER – What is is_not_trusted in sys.foreign_keys?
Let me know your opinion about this subject in the comments section.
Reference: Pinal Dave (https://blog.sqlauthority.com)