Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Command for adding a default constraint

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]; 
like image 387
Randy Minder Avatar asked Nov 29 '10 19:11

Randy Minder


People also ask

How do you add a constraint?

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows. ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...); The basic syntax of an ALTER TABLE command to ADD CHECK CONSTRAINT to a table is as follows.


1 Answers

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; 
like image 190
gbn Avatar answered Oct 16 '22 00:10

gbn