Solved

CHECK CONSTRAINT question

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

729 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