Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

CHECK CONSTRAINT question

Posted on 2013-05-24
4
Medium Priority
?
482 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 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