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?

This should work:

ALTER TABLE t_tableName
ADD newColumn VARCHAR(50)
CONSTRAINT YourContraintName DEFAULT '' NOT NULL

ALTER TABLE t_tableName
ADD newColumn int NOT NULL
CONSTRAINT DF_defaultvalue DEFAULT (1)

***Try like below script-***

ALTER TABLE DEMO_TABLE
ADD Column1 INT CONSTRAINT Def_Column1 DEFAULT(3) NOT NULL,
Column2 VARCHAR(10) CONSTRAINT Def_Column2 DEFAULT('New') NOT NULL;
GO

I would like to add some details:

The most important hint is: **You should never-ever create a constraint without an explicit name!**

**The biggest problem with unnamed constraints**: When you execute this on various customer machines, you will get **different/random names** on each.
Any **future upgrade script** will be a real headache...

**The general advise is:**

* No constraint without a name!
* Use some naming convention e.g.
* `DF_TableName_ColumnName` for a default constraint
* `CK_TableName_ColumnName` for a check constraint
* `UQ_TableName_ColumnName` for a unique constraint
* `PK_TableName` for a primary key constraint

**The general syntax is**

TheColumn <DataType> Nullability CONSTRAINT ConstraintName <ConstraintType> <ConstraintDetails>

**Try this here**

You can add more constraints to each column and you can add additional constraints just as you add columns after a comma:

CREATE TABLE dbo.SomeOtherTable(TheIdThere INT NOT NULL CONSTRAINT PK_SomeOtherTable PRIMARY KEY)
GO
CREATE TABLE dbo.TestTable
(
--define the primary key
ID INT IDENTITY NOT NULL CONSTRAINT PK_TestTable PRIMARY KEY

--let the string be unique (results in a unique index implicitly)
,SomeUniqueString VARCHAR(100) NOT NULL CONSTRAINT UQ_TestTable_SomeUniqueString UNIQUE

--define two constraints, one for a default value and one for a value check
,SomeNumber INT NULL CONSTRAINT DF_TestTable_SomeNumber DEFAULT (0)
CONSTRAINT CK_TestTable_SomeNumber_gt100 CHECK(SomeNumber>100)

--add a foreign key constraint
,SomeFK INT NOT NULL CONSTRAINT FK_TestTable_SomeFK FOREIGN KEY REFERENCES dbo.SomeOtherTable(TheIdThere)

--add a constraint for two columns separately
,CONSTRAINT UQ_TestTable_StringAndNumber UNIQUE(SomeFK,SomeNumber)
);
GO

--insert some data

INSERT INTO dbo.SomeOtherTable VALUES(1);
INSERT INTO dbo.TestTable(SomeUniqueString,SomeNumber,SomeFK) VALUES('hello',111,1);
GO
INSERT INTO dbo.TestTable(SomeUniqueString,SomeNumber,SomeFK)
VALUES('fails due to uniqueness of 111,1',111,1);

I use the following when adding new columns and defaults to large tables.
Execute each line separately:

ALTER TABLE dbo.[TableName] ADD [ColumnName] BIT NULL; /*null>no table rebuild*/

UPDATE rf SET rf.[ColumnName] = 0 FROM dbo.[TableName] rf WHERE rf.[ColumnName] IS NULL;

ALTER TABLE dbo.[TableName] ALTER COLUMN [ColumnName] BIT NOT NULL;

ALTER TABLE dbo.[TableName] ADD CONSTRAINT DF_[TableName]_[ColumnName] DEFAULT 0 FOR [ColumnName];