Can I create a named default constraint in an add column statement in SQL Server?

sql-server constraints alter-table code-maintainability
GlennS picture GlennS · Sep 22, 2010 · Viewed 80.5k times · Source

In SQL Server, I have a new column on a table:

ALTER TABLE t_tableName 
    ADD newColumn NOT NULL

This fails because I specify NOT NULL without specifying a default constraint. The table should not have a default constraint.

To get around this, I could create the table with the default constraint and then remove it.

However, there doesn't appear to be any way to specify that the default constraint should be named as part of this statement, so my only way to get rid of it is to have a stored procedure which looks it up in the sys.default_constraints table.

This is a bit messy/verbose for an operation which is likely to happen a lot. Does anyone have any better solutions for this?

Answer

Joe Stefanelli picture Joe Stefanelli · Sep 22, 2010

This should work:

ALTER TABLE t_tableName 
    ADD newColumn VARCHAR(50)
    CONSTRAINT YourContraintName DEFAULT '' NOT NULL

Related questions

'invalid for creating a default constraint' error when trying to add a constraint to an existing table

I want to add a default constraint using ALTER TABLE in SQL Server, but I received the below error message instead. Column 'StartDate' in table 'Register' is invalid for creating a default constraint. I know that I can declare a …

Read More
Add primary key to existing table

I have an existing table called Persion. In this table I have 5 columns: persionId Pname PMid Pdescription Pamt When I created this table, I set PersionId and Pname as the primary key. I now want to include one more column …

Read More
How can foreign key constraints be temporarily disabled using T-SQL?

Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?

Read More