Solved

Explain this TSQL

Posted on 2010-08-31
4
381 Views
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])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[FormData] CHECK CONSTRAINT [FK_FormData_Units]

GO

Open in new window

0
Comment
Question by:soozh
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
sameer2010 earned 200 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
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 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" ...
0
 

Author Comment

by:soozh
ID: 33565330
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 total points
ID: 33565361
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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now