SQL Server Performance

Foreign Key Properties

Discussion in 'General Developer Questions' started by vnutovich, Jan 13, 2005.

  1. vnutovich New Member

    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
  2. benwilson New Member

    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...
  3. vnutovich New Member

    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...
  4. benwilson New Member

    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?!)

  5. Adriaan New Member

    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.
  6. vnutovich New Member

    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?!)


  7. vnutovich New Member

    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.
  8. FrankKalis Moderator

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

    --
    Frank
    http://www.insidesql.de
  9. vnutovich New Member

    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

  10. FrankKalis Moderator

    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
  11. FrankKalis Moderator

    Btw, it makes also logically sense. When you check this property, without specifying what action to take, where the deeper sense at all?

    --
    Frank
    http://www.insidesql.de
  12. vnutovich New Member

    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

  13. Adriaan New Member

    Vlad,

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

    Adriaan
  14. Adriaan New Member

    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'
  15. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by vnutovich</i><br /><br /> 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. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Now, you're definitely mixing things up a bit. What kind of relationship should this be, where you can child rows with no parent row? Your data integrity will pretty soon be shot. Correction, it doesn't exist at all. You do have read BOL on what ALTER TABLE ...NOCHECK CONSTRAINT means, don't you? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  16. Adriaan New Member

    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=';)' />].
  17. vnutovich New Member

    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

  18. FrankKalis Moderator

    Adriaan, I have read BOL carefully. But who know, I might be missing a part in this puzzle anyway [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  19. Adriaan New Member

    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 ...
  20. vnutovich New Member

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

    thanks everybody for your help.
  21. FrankKalis Moderator

    So, would you mind posting this solution here? I also love to learn new things.

    ...and Adriaan, I didn't take it personally. Obviously I misunderstood Vlad's question. I blame it on the language barrier...

    --
    Frank
    http://www.insidesql.de
  22. Adriaan New Member

    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!
  23. vnutovich New Member

  24. Adriaan New Member

    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.
  25. Bodestone New Member

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

    select ObjectProperty(constraintID, 'CnstIsDisabled')



    Change is inevitable, except from a vending machine

Share This Page