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)
69 Comments. Leave new
site is too slow
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.
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.
5-6 years later and still helping; thanks, google!
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 ‘ ‘
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.
Hi
I want to mention default constraint by using GUI only not with query? please Help me?