SSMS constraint script: Purpose of second alter table statement?

SSMS scripts Foreign Key constraints as two statements:

ALTER TABLE {table}
WITH CHECK
ADD CONSTRAINT {constraintname} {constraint spec}
GO

ALTER TABLE {table}
CHECK CONSTRAINT {constraintname}
GO

What is the purpose of the second statement?

The second line is redundant but I agree the overuse of CHECK in that syntax is confusing.

According to the ALTER TABLE documentation the existing data is checked anyway if the WITH CHECK ADD option is used and the newly added constraint is enabled.


WITH CHECK | WITH NOCHECK

Specifies whether the data in the table is
or is not validated against a newly added or re-enabled FOREIGN KEY or
CHECK constraint. If not specified, WITH CHECK is assumed for new
constraints, and WITH NOCHECK is assumed for re-enabled constraints.

The second line enables the constraint, again from the documentation, but that constraint was already enabled.


{ CHECK | NOCHECK } CONSTRAINT Specifies that constraint_name is
enabled or disabled. This option can only be used with FOREIGN KEY and
CHECK constraints. When NOCHECK is specified, the constraint is
disabled and future inserts or updates to the column are not validated
against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE
constraints cannot be disabled.

However, the constraint is already enabled as can be evidenced from this code:

CREATE TABLE [dbo].[constraintdemo](
    [constraintfield] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[constraintdemo]  WITH CHECK ADD  CONSTRAINT [CK_constraintdemo] CHECK  (([constraintfield]>(0)))
GO

INSERT INTO constraintdemo (constraintfield) VALUES (-1)

Which results in:


Msg 547, Level 16, State 0, Line 12 The INSERT statement conflicted
with the CHECK constraint "CK_constraintdemo". The conflict occurred
in database "PlayGround", table "dbo.constraintdemo", column
'constraintfield'. The statement has been terminated.

See this dbfiddle where you can run the above code

If you want evidence that the existing data is effectively validated by the WITH CHECK you can run this code:

CREATE TABLE [dbo].[constraintdemo](
    [constraintfield] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO constraintdemo (constraintfield) values (-1),(-2)
GO

ALTER TABLE [dbo].[constraintdemo]  WITH CHECK ADD  CONSTRAINT [CK_constraintdemo] CHECK  (([constraintfield]>(0)))
GO

Which returns:


(2 row(s) affected) Msg 547, Level 16, State 0, Line 21 The ALTER
TABLE statement conflicted with the CHECK constraint
"CK_constraintdemo". The conflict occurred in database "PlayGround",
table "dbo.constraintdemo", column 'constraintfield'.

Again: dbfiddle

In this example it doesn't matter if it's a check constraint or a foreign key constraint as evidenced by this somewhat more complicated example (note that I commented out the second line):

CREATE TABLE [dbo].[keytable](
    [key] [nchar](10) NOT NULL,
 CONSTRAINT [PK_keytable] PRIMARY KEY CLUSTERED 
(
    [key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[valuetable](
    [key] [nchar](10) NULL,
    [value] [nchar](10) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[valuetable]  WITH CHECK ADD  CONSTRAINT [FK_valuetable_keytable] FOREIGN KEY([key])
REFERENCES [dbo].[keytable] ([key])
GO

--ALTER TABLE [dbo].[valuetable] CHECK CONSTRAINT [FK_valuetable_keytable]
GO

INSERT INTO keytable ([key]) VALUES (1),(2);
GO

INSERT INTO valuetable ([key],[value]) VALUES (3,'test');
GO

This still results in an error showing the foreign key is enabled:


Msg 547 Level 16 State 0 Line 1 The INSERT statement conflicted with
the FOREIGN KEY constraint "FK_valuetable_keytable". The conflict
occurred in database "fiddle_7378e515ee284b358b4e2edbc07d1329", table
"dbo.keytable", column 'key'. Msg 3621 Level 0 State 0 Line 1

The statement has been terminated.

Dbfiddle here

And again we can turn things around to prove that it checks existing data on creation using this code:

CREATE TABLE [dbo].[keytable](
    [key] [nchar](10) NOT NULL,
 CONSTRAINT [PK_keytable] PRIMARY KEY CLUSTERED 
(
    [key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[valuetable](
    [key] [nchar](10) NULL,
    [value] [nchar](10) NULL
) ON [PRIMARY]

GO
INSERT INTO keytable ([key]) VALUES (1),(2);
GO

INSERT INTO valuetable ([key],[value]) VALUES (3,'test');
GO

ALTER TABLE [dbo].[valuetable]  WITH CHECK ADD  CONSTRAINT [FK_valuetable_keytable] FOREIGN KEY([key])
REFERENCES [dbo].[keytable] ([key])
GO

Which returns:


Msg 547 Level 16 State 0 Line 1 The ALTER TABLE statement conflicted
with the FOREIGN KEY constraint "FK_valuetable_keytable". The conflict
occurred in database "fiddle_59761b4587c14f2b96b8029a10de6229", table
"dbo.keytable", column 'key'.

dbfiddle here

StackExchange Database Administrators Q#183736, answer score: 6