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]&gt;(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]&gt;(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