August 17, 2010 at 9:55 am
Same foreign key in two differnt servers behaves differently.
1 st server : when scripted foreign key it gave
ADD constarint with NOCHECK
alter table CHECK constraint FK_name
2nd server : when scripted it gave
ADD constraint with CHECK
I believe functionality is same on both the servers, but why its scripting differently .How to make the 1 st one with CHECK .
August 18, 2010 at 6:16 am
Please advice ...
August 18, 2010 at 7:03 am
SQL_DBA_3 (8/18/2010)
Please advice ...
Hornestly, there is no much to advise here...
You believe functionality is the same on both the servers while FK are scripting differently in terms of CHECK vs NOCHECK?
Read Forcing a FOREIGN KEY Constraint by Using WITH NOCHECK section in this article:
http://msdn.microsoft.com/en-us/library/ms177463.aspx
to understand the differnec between them.
If you want FK's to be the same on both servers, script them out (together with with "drop" part) from one which you like more and use it on the second one.
August 18, 2010 at 7:25 am
Thanks ..
I was looking for an reason why its behaving differently...Is there data mismatch which is foricng it for NOCHECK and then enabling the CHECK....
I wanted the foreign key to script out with the CHECK option....instead of NO CHECK followed by CHECK ...
August 18, 2010 at 7:43 am
SQL_DBA_3 (8/18/2010)
Thanks ..I was looking for an reason why its behaving differently...Is there data mismatch which is foricng it for NOCHECK and then enabling the CHECK....
I wanted the foreign key to script out with the CHECK option....instead of NO CHECK followed by CHECK ...
NO, they just were created differently in the first place.
I don't know did someone manually scripted CREATE or SSMS was used.
To make it NOCHECK, you would need explcitly request it in CREATE script (as default option there is CHECK).
In SSMS the first thing to set when you create FK is a General option for: "Check Existing Data On Creation Or Re-Enabling", by default it is set to "Yes", but you can change it to be "NO".
August 19, 2010 at 5:58 am
As I understand what you're saying, you have two databases, each with an identical foreign key constraint, which, as far as you know, are behaving identically.
For each, you have used some tool, or tools, to automatically generate a script that will recreate the foreign key. You are surprised that the identical constraints have resulted in two different scripts, although you believe that the result of running the scripts are the same.
Could it be that you used two different tools to create the scripts?
Just now I used Query Analyzer from SQL Server 2000, and Management Studio from SQL Server 2008 to generate scripts to create the same table from the same database from the same server. The two different tools created two different scripts. The first simply added a constraint clause, with CHECK, to the end of the CREATE TABLE statement. The other created the table without the constraint, and then added the constraint with NOCHECK, and then ran the CHECK, all as separate statements separated by GO.
Might the cause be something similar in your case?
August 19, 2010 at 8:47 am
Check both systems for Untrusted constraints; it's very common to disable a constraint, add data, and re-enable it without the required "WITH CHECK CHECK" syntax.
SELECT
CASE OBJECTPROPERTY(soConstraint.id,'CnstIsNotTrusted')
WHEN 1 THEN 'Untrusted'
ELSE 'Trusted'
END AS TrustedConstraint
, CASE OBJECTPROPERTY(soConstraint.id,'CnstIsDisabled')
WHEN 1 THEN 'Disabled'
ELSE 'Enabled'
END AS EnabledConstraint
,soParent.name AS ParentName
,soConstraint.name AS ConstraintName
,soParent.id AS ParentID
,soConstraint.id AS ConstraintID
,soConstraint.status
FROM sysobjects soConstraint
LEFT OUTER JOIN sysobjects soParent
ON soParent.id = soConstraint.parent_obj
WHERE soConstraint.xtype IN ('F','C') -- foreign key and check constraints can be untrusted
--AND soParent.name = 'table name'
ORDER BY TrustedConstraint DESC, EnabledConstraint, soParent.name, soConstraint.name
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply