Solved

CHECK CONSTRAINT question

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

726 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