Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 801
GlennS Avatar asked Sep 22 '10 14:09

GlennS


People also ask

How do I add a DEFAULT constraint to an existing table?

To add a DEFAULT constraint to an existing column, use the ALTER TABLE statement and specify the column and the specific constraint that you want to apply.

How do you name a DEFAULT constraint in SQL Server?

In SQL Server, the default constraint names start with specific prefixes: PK , UQ , CK , or FK . The default name for a PRIMARY KEY constraint starts with ' PK ', followed by underscores (' __ '), the table name, more underscores (' __ '), and a hexadecimal sequence number generated by SQL Server.


1 Answers

This should work:

ALTER TABLE t_tableName      ADD newColumn VARCHAR(50)     CONSTRAINT YourContraintName DEFAULT '' NOT NULL 
like image 163
Joe Stefanelli Avatar answered Oct 02 '22 18:10

Joe Stefanelli