Explain this TSQL

Posted on 2010-08-31
Medium Priority
Last Modified: 2012-05-10
Below is some TSQL from a script that has been generated by Management Studio.

The first command "CHECK ADD CONSTRAINT" creates a foreign key constration, and I guess forces a cascade delete.

What does the second command "CHECK CONSTRAINT "  do?

I am porting the database to a compact database i want to be sure i have included everything.
/****** Object:  ForeignKey [FK_FormData_Units]    Script Date: 08/31/2010 10:16:18 ******/
ALTER TABLE [dbo].[FormData]  WITH CHECK ADD  CONSTRAINT [FK_FormData_Units] FOREIGN KEY([uni_id])
REFERENCES [dbo].[Units] ([uni_id])
ALTER TABLE [dbo].[FormData] CHECK CONSTRAINT [FK_FormData_Units]

Open in new window

Question by:soozh
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
  • 2
LVL 13

Accepted Solution

sameer2010 earned 800 total points
ID: 33565256
This will add the check constraint called FK_FormData_Units which will validate if the uni data exists in units table and on deletion of row from units, would cascade the deletion to FormData table as well.
It would then execute the constraint FK_FormData_Units
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 33565275
the second statement forces the contraint to be checked (again) explicitly...
because you could create a constraint with telling "don't check the data right now" ...

Author Comment

ID: 33565330
does this mean that in my case the second statement is not required?

My sql for the compact database is :

FOREIGN KEY(uni_id) REFERENCES Units(uni_id)

Do i need to add anything?
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 33565361
you can create / alter a constraint to be NOCHECK:

means: it exists, but is not checked by the sql engine.
with the ALTER, you can change that to be CHECKED, which will do 2 things:
* check the data, if it is valid in regards to the constraint
* if the data IS valid, enables the constraint, so any further data changes/inserts will be checked against the constraint automatically

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

741 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