Command for adding a default constraint

by

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;

Leave a Comment