Search code examples
sql-serverdefault-constraint

Problem with default constraint "with values" in sql server

I came to know that in Sql Server we have WITH VALUES that sets data inside NULL columns with specified values inside default constraint. But my query is not setting values in column that have already NULL in it,even when i used WITH VALUES, Please correct the query if I am wrong. my query is

GO
CREATE TABLE [dbo].[DefaultTest]
(
    [id] [int] NULL
) 
GO
INSERT INTO [dbo].[DefaultTest]
SELECT NULL
GO
ALTER TABLE [dbo].[DefaultTest] 
ADD  CONSTRAINT [con_Test]  DEFAULT 1 FOR [id]
WITH VALUES
GO
SELECT * FROM [DefaultTest]
GO
DROP TABLE [DefaultTest]

Upon executing the query i noticed that still the column contains only NULL instead of 1.

Solution

  • MSDN

    [WITH VALUES] is used when you adding new column and it should have some value. If you don't adding new column by [ALTER] operator then [WITH VALUES] option will be ignored.

    If this is used when the related column isn't also being added then it has no effect.