Foreign Key Properties

Can anybody please tell me how I can get information about foreign keys? Specifically I#%92m looking to know whether “Enforce relationship for INSERTs and UPDATEs” property is set. For some reason I can get most properties using OBJECTPROPERTY function but not the one I mentioned.

Thank you,

Vlad

From enterprise manager:
Right click the table, Select Design table and then click the 'Manage Relationships' button. Select the relationship you are interested in the the details are displayed below. Not sure how to do this from TSQL though...

Thanks Ben for your quick respond and you are right; I#%92m looking for the property using TSQL.

quote:Originally posted by benwilson

From enterprise manager:
Right click the table, Select Design table and then click the 'Manage Relationships' button. Select the relationship you are interested in the the details are displayed below. Not sure how to do this from TSQL though...

The output from the procedure sp_help <tableName> includes a section on Foreign Keys and details the action for Delete and update and a 3rd column 'status_enabled'. At a guess, i would say 'status enabled' is for insert (possibly?!)

You can call sp_foreign_keys_rowset with the table name to get just the info you need. Unfortunately there is no documentation in BOL (so don't rely on it being available in future versions, blabla) but it is available in the master database.

Yes, status_enables is a flag for enforcing relationship; unfortunately it will not do me any good since I need get the same value from system tables/functions and possibly SP#%92s.

quote:Originally posted by benwilson

The output from the procedure sp_help <tableName> includes a section on Foreign Keys and details the action for Delete and update and a 3rd column 'status_enabled'. At a guess, i would say 'status enabled' is for insert (possibly?!)

Even if I did call this SP or sp_fkeys or sp_foreignkeys all of them don#%92t have the property I#%92m looking for.

quote:Originally posted by Adriaan

You can call sp_foreign_keys_rowset with the table name to get just the info you need. Unfortunately there is no documentation in BOL (so don't rely on it being available in future versions, blabla) but it is available in the master database.

Yes I did and my property isn't there either.

quote:Originally posted by FrankKalis

Can I assume you already tried SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS?

--
Frank
http://www.insidesql.de

This property does exist and I can control it programmatically by writing an extra line of code "ALTER TABLE dbo.[table name] NOCHECK CONSTRAINT [FK name]". The sole purpose of this property is to have strict relationship, meaning a primary row has to exist or so called "loose relationship" that doesn't have to have corresponding primary row. And the cascading properties are just an extension of "Enforce relationship for INSERTs and UPDATEs" when it is set.

quote:Originally posted by FrankKalis

I think you're misunderstanding something here.
The "property" you're after does not to the best of my knowledge exists. This is only a nice GUI trick to enable or disable the real important properties. So, when you defined a cascading action, implicitely this property is set. When no action is defined, the check mark in EM isn't set either. That's exactly what is stated in the information_schema view. To check this, you can have a play with it in EM. Create a new relationship in EM, and generate a change script. Have a play with the various check options and again, generate a change script again. Now, when you only set “Enforce relationship for INSERTs and UPDATEs” without any further action and generate a change script, you actually should find that nothing changes at all.

--
Frank
http://www.insidesql.de

Vlad,

Could you post some CREATE TABLE scripts to illustrate the sort of relationship that you're talking about?

Adriaan

Another undocumented sp from the master database - this one is probably the one you're looking for:

sp_check_constraints_rowset 'MyTable'

[edit:]
Nope, it's expecting the constraint name:
sp_check_constraints_rowset 'MyConstraint'

Frank,<br /><br />If you read BOL carefully, it says:<br /><b>If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. This is not recommended except in rare cases. The new constraint will be evaluated in all future updates. Any constraint violations suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.</b><br /><br />So yes, you can add a constraint to a table without checking the existing data, but it is not recommended. If Vlad wants to have a workaround to insert rows that do not comply to a constraint, then indeed he can drop the constraint, insert the data and re-create the constraint with the NOCHECK option. But of course that's not wise.<br /><br />And I'm pretty sure there's an Eierkuchen saying for exactly this situation[<img src='/community/emoticons/emotion-5.gif' alt='' />].

Here is the script I have so far:



SELECTSO3.Name AS FKey,
'FK_' + SO4.Name + '_' + SO2.Name + '_' + SC.Name,
'ALTER TABLE dbo.' + SO4.Name + ' DROP CONSTRAINT ' + SO3.Name AS DropScr,
'ALTER TABLE dbo.' + SO4.Name + ' WITH NOCHECK ADD CONSTRAINT ' + SO3.Name + ' FOREIGN KEY(' + SC.Name +') REFERENCES DBO.' + SO2.Name + '(' + SC2.Name + ')'
+
CASE WHEN OBJECTPROPERTY(SO3.id, 'CnstIsDeleteCascade') = 1 THEN 'ON DELETE CASCADE' ELSE '' END +
CASE WHEN OBJECTPROPERTY(SO3.id, 'CnstIsUpdateCascade') = 1 THEN 'ON DELETE UPDATE' ELSE '' END

--I AM MISSING THIS PART
+
CASE
WHEN '“Enforce relationship for INSERTs and UPDATEs”' = '“Enforce relationship for INSERTs and UPDATEs”' THEN
'ALTER TABLE DBO.' + SO2.Name + ' NOCHECK CONSTRAINT FK_JournalCategories_Companies_CompanyID' ELSE ''
END

AS AddScr,
SO2.Name PKTable, SO4.Name AS FKTable, SC2.Name AS KeyID
FROM SYSFOREIGNKEYS SFK
JOIN SYSCOLUMNS SC ON SC.ID = SFK.FKeyID AND
SC.ColID = SFK.FKey
JOIN SYSOBJECTS SO ON SO.ID = SC.ID
JOIN SYSCOLUMNS SC2 ON SC2.ID = SFK.RKeyID AND
SC2.ColID = SFK.RKey
JOIN SYSOBJECTS SO2 ON SO2.ID = SC2.ID
JOIN SYSOBJECTS SO3 ON SO3.ID = SFK.ConstID
JOIN SYSOBJECTS SO4 ON SO4.ID = SO3.Parent_Obj

Frank, apologies if the criticism seemed to be aimed at you - that was certainly not my intention.

Vlad's question can be rephrased as: can you tell if the WITH NOCHECK option was used when the constraint was created? The answer is of course a resounding no. But if you do find data in the table that does not satisfy the constraint, then the WITH NOCHECK option was definitely used when the constraint was created.

If there is only data that satisfies the constraint, then there is no way of telling - at least not without the actual script that was used ...

Finally, there was somebody knowledgeable enough on another discussion list who knew the correct answer to my question.

thanks everybody for your help.

Vlad,

Could you post the correct answer? If we haven't understood your question right there's obviously something for us to learn. Thanks in advance!

Here is the link to another discussion:
http://www.windowsitpro.com/SQLServer/forums/messageview.cfm?catid=1690&threadid=129196

From user ScottPletcher at the aforementioned site:

quote:OBJECTPROPERTY(constraintId, 'CnstIsDisabled') will tell you whether or not a particular constraint is disabled (= NOCHECK, I presume).

And indeed this property can be found in BOL under the OBJECTPROPERTY function.
From BOL on ALTER TABLE, the following command disables a constraint (similar to disabling a trigger):
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
You can disable a constraint unless it is defined as PRIMARY KEY or UNIQUE.

or for those that don't want to trawl for it

select ObjectProperty(constraintID, 'CnstIsDisabled')



Change is inevitable, except from a vending machine