Adding a DEFAULT constraint to an existing column

  • DEFAULT Constraint can be added when the table is created, as part of the table definition.
  • DEFAULT Constraint can be added to an existing table
How to CREATE DEFAULT Constraint on existing table?
To explain this, First I will create a table and then inserts a record (value into the first column and NULL into second column).
CREATE TABLE dbo.TestDefault(
column1 [int],
column2 [int]
)
GO
INSERT INTO dbo.TestDefault (column1) VALUES (10)
GO

Now I will add a DEFAULT constraint to the second column using below T-SQL code:
ALTER TABLE dbo.TestDefault
ADD CONSTRAINT TestDefault_DC_Columns2
DEFAULT 99 FOR column2
GO

To verify whether the default is applied or not, another value is inserted into the first column and the table is queried:
INSERT INTO dbo.TestDefault (column1) VALUES (20)
GO
SELECT * FROM TestDefault
GO 

 

No comments:

Post a Comment