Solved

CHECK CONSTRAINT question

Posted on 2013-05-24
4
470 Views
Last Modified: 2013-06-09
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?
0
Comment
Question by:purplesoup
  • 3
4 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
ID: 39195393
"ALTER TABLE Table1 CHECK CONSTRAINT FK_Table2_Table1;

doesn't do anything"


Apparently...and for sure if the tables are empty it won't do anything however if you have data in parent and child table the CHECK CONSTRAINT will ensure no orphans can exists in child table.

Hint - on large tables add the FK - foreign keys - WITH NOCHECK and CHECK CONSTRAINT  later - otherwise it may take forever to add it and on live OLTP system may impact performance.
0
 

Author Comment

by:purplesoup
ID: 39200518
I understand CHECK CONSTRAINT will ensure there are no orphans, but from tests I have done this happens with the first statement:

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.
0
 

Accepted Solution

by:
purplesoup earned 0 total points
ID: 39200532
Incidentally from tests I ran to try to understand this a bit better, if the original constraint was added with a NOCHECK and a subsequent ALTER TABLE.. WITH CHECK is run, it won't even validate the existing data as you suggest, you need to run a WITH CHECK CHECK statement for that.
0
 

Author Closing Comment

by:purplesoup
ID: 39232783
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.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question