5

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?

6

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

  • 2
    Tom, thanks for doing the proofs. It sounds like the second statement is superfluous. I wanted to make sure I wasn't missing something, and even worse - guiding our team into bad habits based on my own. – Paul Holmes Aug 17 '17 at 23:48
-1

When you add a FK constraint to a table with data already in it, it's untrusted. New data will be verified, but existing data will not be. The second command tells the engine to verify that the constraint is valid (no violations) and then it is marked as trusted. This article, from Brent Ozar (not affiliated) goes into more detail. https://www.brentozar.com/blitz/foreign-key-trusted/

  • That would be WITH CHECK CHECK CONSTRAINT (notice the double CHECK) – Tom V Aug 17 '17 at 14:13
  • 3
    The documentation mentions "WITH CHECK is assumed for new constraints" so it will be trusted by default. – Dan Guzman Aug 17 '17 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.