Solved

CHECK CONSTRAINT question

Posted on 2013-05-24
4
465 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now