SQL SERVER – Add Column With Default Column Constraint to Table

Just a day ago while working with database Jr. A developer asked me question how to add a column along with a column constraint. He also wanted to specify the name of the constraint. The newly added column should not allow NULL value. He requested my help as he thought he might have to write many lines to achieve what was requested.

It is very easy to add columns and specify default constraints. I have seen many examples where the constraint name is not specified, if constraint name is not specified SQL Server will generate a unique name for itself. I prefer to specify my constraint name as per my coding standards. You can read my coding standard here: SQL SERVER Database Coding Standards and Guidelines Complete List Download

ALTER TABLE TestTable
ADD NewCol VARCHAR(50)
CONSTRAINT DF_TestTable_NewCol DEFAULT '' NOT NULL
GO

Here are few of the related blog posts on this subject.

• How to Disable and Enable All Constraint for Table and Database
  
• How to Disable and Enable All Constraint for Table and Database
• Create Unique Constraint on Table Column on Existing Table

Please leave a comment about what you think about this blog post about column constraint.

Reference : Pinal Dave (https://blog.sqlauthority.com)