ALTER TABLE CHECK CONSTRAINT after ALTER TABLE WITH CHECK ADD CONSTRAINT

PROBLEM:

Do you need to add ALTER TABLE CHECK CONSTRAINT statement after ALTER TABLE WITH CHECK ADD CONSTRAINT statement?

SOLUTION:

Let's check:

CREATE TABLE AAA
(
       ID_AAA1 INT,
       ID_AAA2 INT,
       NAME NVARCHAR(50),
       CONSTRAINT PK_1 PRIMARY KEY CLUSTERED (ID_AAA1,ID_AAA2)
)

CREATE TABLE BBB
(
       ID_BBB1 INT,
       ID_BBB2 INT,
       NAME NVARCHAR(50),
       CONSTRAINT PK_2 PRIMARY KEY CLUSTERED (ID_BBB1,ID_BBB2)
)

ALTER TABLE [dbo].[BBB]  WITH CHECK ADD  CONSTRAINT [FK_1] FOREIGN KEY([ID_BBB1],[ID_BBB2])
REFERENCES [dbo].[AAA] ([ID_AAA1],[ID_AAA2])
GO

INSERT AAA(ID_AAA1, ID_AAA2) VALUES (1,1);
INSERT AAA(ID_AAA1, ID_AAA2) VALUES (2,2);

-- The following INSERT statement is OK
INSERT BBB(ID_BBB1, ID_BBB2) VALUES (1,1);
-- The following INSERT statement conflicted with the FOREIGN KEY constraint "FK_1"
INSERT BBB(ID_BBB1, ID_BBB2) VALUES (3,3);

ALTER TABLE {CHECK | NOCHECK} CONSTRAINT 

Specifies that constraint_name is enabled or disabled. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions.

ALTER TABLE {WITH CHECK | WITH NOCHECK} ADD CONSTRAINT

If you want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH CHECK.

It is not recommend using WITH NOCHECK, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

By default, when you add a constraint, its enable, so you don't need to add ALTER TABLE CHECK CONSTRAINT statement after it.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!