Command for adding a default constraint

sql sql-server tsql default-constraint
Randy Minder picture Randy Minder · Nov 29, 2010 · Viewed 54.5k times · Source

There appears to be at least two ways to add a default constraint using straight T-SQL. Am I correct that the only difference between the two below is that the second method specifically creates a name for the constraint, and the first method has one generated by SQL Server?

ALTER TABLE [Common].[PropertySetting] ADD DEFAULT ((1)) FOR [Active];
ALTER TABLE [Common].[PropertySetting] ADD CONSTRAINT [DF_PropertySetting_Active) DEFAULT ((1)) FOR [Active];

Answer

gbn picture gbn · Nov 29, 2010

Pretty much, yes for an ALTER TABLE

You can add a columnn with default in one step for CREATE or ALTER too.

ALTER TABLE foo ADD bar varchar(100) CONSTRAINT DF_Foo_Bar DEFAULT ('bicycle')
ALTER TABLE foo ADD bar varchar(100) DEFAULT ('bicycle')

As you noted, the system generates a name if one is not supplied. CONSTRAINT constraint_name is optional says MSDN. The same applies to any column or table CONSTRAINT

Edit If the column was already created, and you only want to add the constraint use:

ALTER TABLE TableName ADD CONSTRAINT DF_Foo_Bar DEFAULT 'bicycle' FOR FieldName;

Related questions

How to add a default value to an already existing column?

I have an existing column in my SQL Server database. I have tried about everything I can think of but can not get a default value to be added to the column. What works in every other database is alter …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More