Sql-server – Is ALTER TABLE CHECK CONSTRAINT redundant

check-constraintsconstraintsql serversql server 2014ssms

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.

Best Answer

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.

Related Question