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

SQL SERVER - Add Column With Default Column Constraint to Table constraint-800x144

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

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

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

SQL Coding Standards, SQL Constraint and Keys, SQL Scripts, SQL Server
Previous Post
SQL SERVER – 2005 – Analysis Services Query Performance Top 10 Best Practices
Next Post
SQLAuthority News – Book Review – Joe Celkos SQL Puzzles and Answers, Second Edition, Second Edition

Related Posts

69 Comments. Leave new

  • site is too slow

    Reply
  • I want to check the default constraint is exist or not , If not I will add else ignore
    I have query like below but if I run multiple times it gives me error.

    if not(exists(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘orderhdr’ AND COLUMN_NAME = ‘stphone’ AND COLUMN_DEFAULT IS NOT NULL))
    begin
    ALTER TABLE [dbo].[orderhdr] ADD DEFAULT ((‘ ‘)) FOR stphone
    End

    Regards,
    Chakradhar.

    Reply
  • Hi,
    I want to check the default constraint exist or not , Have below query it’s giving me error if I run multiple times.

    if not(exists(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘orderhdr’ AND COLUMN_NAME = ‘stphone’ AND COLUMN_DEFAULT IS NOT NULL))
    begin
    ALTER TABLE [dbo].[orderhdr] ADD DEFAULT ((‘ ‘)) FOR stphone
    End

    Regards,
    Chakradhar.

    Reply
  • 5-6 years later and still helping; thanks, google!

    Reply
  • Hi, I have a question. If you add a column A TEXT(8) with a default value ‘ ‘, when you insert a row next time, this column A is set to ‘ ‘, while the problem is that the existing rows will not be set to ‘ ‘.

    This problem happens in MDB, when I ad a column to a table with default ‘ ‘, the new added column’s value is null, not the default value ‘ ‘, only when I insert a new row, then the column will be set to ‘ ‘

    Reply
  • Hi i have a scenario…will you please ans it ?

    i have a flat file as source having 10000 rows. i have to move it to destination as csv file. and i need to skip some top most rows and bottom rows some % ( varying ). will you please solve this?

    hope you got it :)

    Thanks in advance.

    Reply
  • Hi

    I want to mention default constraint by using GUI only not with query? please Help me?

    Reply

Leave a Reply