CHECK CONSTRAINT question
In some Microsoft SQL Server scripts you sometimes see something like this:
ALTER TABLE Table1
WITH CHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY (COL2)
REFERENCES Table2 (COL2);
ALTER TABLE Table1 CHECK CONSTRAINT FK_Table2_Table1;
Where the tables look something like this:
CREATE TABLE Table1
(COL1 int CONSTRAINT PK_Table1 PRIMARY KEY ,
COL2 int);
CREATE TABLE Table2
(COL2 int CONSTRAINT PK_Table2 PRIMARY KEY,
COL3 varchar(10)
);
As I understand it, the second statement:
ALTER TABLE Table1 CHECK CONSTRAINT FK_Table2_Table1;
doesn't do anything. Can anyone explain if there is a reason for it, and if so what it is for?
ALTER TABLE Table1
WITH CHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY (COL2)
REFERENCES Table2 (COL2);
ALTER TABLE Table1 CHECK CONSTRAINT FK_Table2_Table1;
Where the tables look something like this:
CREATE TABLE Table1
(COL1 int CONSTRAINT PK_Table1 PRIMARY KEY ,
COL2 int);
CREATE TABLE Table2
(COL2 int CONSTRAINT PK_Table2 PRIMARY KEY,
COL3 varchar(10)
);
As I understand it, the second statement:
ALTER TABLE Table1 CHECK CONSTRAINT FK_Table2_Table1;
doesn't do anything. Can anyone explain if there is a reason for it, and if so what it is for?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
lcohan confirmed my opinion that a second CHECK CONSTRAINT doesn't do anything, but suggested that if it was run after a NOCHECK it would do something. I have included my comment as this is not the case - you need to run a CHECK CHECK CONSTRAINT to validate after a NOCHECK CONSTRAINT was added.
ASKER
ALTER TABLE Table1
WITH CHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY (COL2)
REFERENCES Table2 (COL2);
I haven't been able to identify what purpose the second CHECK CONSTRAINT serves.