4

I created a table with a foreign key constraint on it. When I generate a table creation script through SSMS, it creates the following code:

ALTER TABLE [dbo].[MainTable]  WITH CHECK ADD  CONSTRAINT [FK_MainTable_ForeignKeyTable] FOREIGN KEY([FK_Field])
REFERENCES [dbo].[ForeignKeyTable] ([PK_Field])
GO

ALTER TABLE [dbo].[MainTable] CHECK CONSTRAINT [FK_MainTable_ForeignKeyTable]
GO

Since the first statement creates the constraint WITH CHECK, does this make the second statement redundant? If not, what is the CHECK CONSTRAINT accomplishing that the WITH CHECK does not?

I saw ALTER TABLE CHECK CONSTRAINT, but in that case, the SSMS is generating the first statement WITH NOCHECK, so it makes sense to have both statements.

  • Similar, but based on what's in that question, I don't know how the original constraint was built. It could have been WITH CHECK or WITH NOCHECK. However, one of the answers linked to another question: stackoverflow.com/questions/529941/…, which it is identical to. If I'm understanding the answers there, then not only is the WITH CHECK redundant, because it's default behavior, but the entire second statement is also redundant, since the constraint is enabled by default. – mathewb Sep 13 '17 at 18:36
  • The WITH CHECK ADD CONSTRAINT in the 1st ALTER and the CHECK CONSTRAINT in the 2nd do (related but) different things. – ypercubeᵀᴹ Sep 12 '18 at 19:04
3

The first statement alters the table to add the constraint. The second statement enables or disables the constraint for future inserts or updates.

So, ALTER TABLE ... CHECK CONSTRAINT ... enables the constraint for future inserts/updates. If you specify ALTER TABLE ... NOCHECK CONSTRAINT ..., that disables the constraint, thereby allowing future inserts and updates to succeed even if the column in the table referenced by the foreign key does not contain a matching value. You can see the utility of having both statements present through this example:

USE tempdb;

IF OBJECT_ID(N'dbo.b', N'U') IS NOT NULL
DROP TABLE dbo.b;
IF OBJECT_ID(N'dbo.a', N'U') IS NOT NULL
DROP TABLE dbo.a;

CREATE TABLE dbo.a
(
    a_ID int NOT NULL
        CONSTRAINT a_primary_key
        PRIMARY KEY
        CLUSTERED
    , SomeData varchar(10) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.a (a_ID, SomeData)
VALUES (1, 'aaaa')
    , (2, 'bbbb')
    , (3, 'cccc');

CREATE TABLE dbo.b
(
    b_ID int NOT NULL
        CONSTRAINT b_primary_key
        PRIMARY KEY
        CLUSTERED
    , SomeData varchar(10) NOT NULL
    , a_ID int NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.b (b_ID, SomeData, a_ID)
VALUES (49, 'dddd', 1)
    , (50, 'eeee', 2)
    , (51, 'ffff', 3);

So, we created two tables that do not have a defined relationship. Later, we decide we want to design-in the relationship, so we add a foreign key in dbo.b that references the primary key column in dbo.a. We want the existing rows to be checked for validity, but don't care about new rows that might get added in future1.

ALTER TABLE dbo.b 
WITH CHECK
ADD CONSTRAINT b_a_foreign_key
FOREIGN KEY (a_ID)
REFERENCES a(a_ID);

ALTER TABLE dbo.b 
NOCHECK CONSTRAINT b_a_foreign_key;

All good. The existing rows were validated, and new rows won't be validated against the foreign key:

INSERT INTO dbo.b (b_ID, SomeData, a_ID)
VALUES (52, 'gggg', 4);

The table contents:

SELECT *
FROM dbo.b
    LEFT JOIN dbo.a ON b.a_ID = a.a_ID

Results:

╔══════╦══════════╦══════╦══════╦══════════╗
║ b_ID ║ SomeData ║ a_ID ║ a_ID ║ SomeData ║
╠══════╬══════════╬══════╬══════╬══════════╣
║   49 ║ dddd     ║    1 ║ 1    ║ aaaa     ║
║   50 ║ eeee     ║    2 ║ 2    ║ bbbb     ║
║   51 ║ ffff     ║    3 ║ 3    ║ cccc     ║
║   52 ║ gggg     ║    4 ║ NULL ║ NULL     ║  <-- crazy stuff
╚══════╩══════════╩══════╩══════╩══════════╝

So, since the code that scripts tables in SSMS has to cover every eventuality, it spits out a ALTER TABLE ... CHECK CONSTRAINT ... even if it's not required, and in the vast majority of cases is certainly not required.


1 - why anyone would want existing rows to be validated, but not care about new rows is a little mysterious, but there you go.

  • 2
    Related q: dba.stackexchange.com/questions/167861/… – ypercubeᵀᴹ Sep 12 '18 at 18:21
  • 1
    Thanks for going beyond the scope of my original question, though I think that the answer I'm looking for is in your final statement - if I can take a little liberty with what you've said, in this particular case, SSMS does not have enough intelligence to determine whether or not the second statement is necessary, but it at least knows enough that the second statement will not alter the behavior of the constraint in an undesirable fashion. So, in order to ensure proper enforcement of the constraint, scripting out the constraint produces both statements. Is that a correct statement? – mathewb Sep 13 '18 at 3:22
  • That's exactly correct. – Max Vernon Sep 13 '18 at 12:46

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.