86

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];
Randy Minder
  • 47,200
  • 49
  • 204
  • 358

1 Answers1

111

Pretty much, yes for an ALTER TABLE

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

ALTER TABLE dbo.TableName
    ADD bar varchar(100) CONSTRAINT DF_Foo_Bar DEFAULT ('bicycle');

ALTER TABLE dbo.TableName
    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

If the column was already created, and you only want to add a (named) DEFAULT constraint, then use:

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

To have the system generate the DEFAULT constraint name (which will be of the form DF_{TableName}_{Column}_{8RandomChars}, e.g. DF_TableName_FieldName_12345678) then omit the CONSTRAINT <name> part, like so:

ALTER TABLE dbo.TableName
    ADD DEFAULT 'bicycle' FOR FieldName;
Dai
  • 141,631
  • 28
  • 261
  • 374
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 3
    Can I use a generated name instead of `DF_Foo_Bar`, even if the column already exists? – slartidan Jan 29 '16 at 14:17
  • @slartidan Yes, use the syntax `ALTER TABLE dbo.TableName ADD DEFAULT(SYSUTCDATETIME()) FOR Created;` to create a system-named constraint object. Note that the "`CONSTRAINT`" keyword must be omitted (which is confusing). – Dai Sep 09 '22 at 00:24