Explain this TSQL

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])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FormData] CHECK CONSTRAINT [FK_FormData_Units]
GO

Open in new window

soozhAsked:
Who is Participating?
 
sameer2010Connect With a Mentor Commented:
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
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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" ...
0
 
soozhAuthor Commented:
does this mean that in my case the second statement is not required?

My sql for the compact database is :

ALTER TABLE FormData
ADD CONSTRAINT FK_FormData_Units
FOREIGN KEY(uni_id) REFERENCES Units(uni_id)
ON DELETE CASCADE ;

Do i need to add anything?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
exactly.
you can create / alter a constraint to be NOCHECK:
http://msdn.microsoft.com/en-us/library/ms190273.aspx

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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.