Post reply

Multiple Constraints(FKs) Defined for the Same Relationship

  • January 6, 2011 at 7:17 am

    #141990

    Hi all,

    I cannot seem to find the value of multiple FKs defined for the same parent-child key combination between two tables. From my point of view, this can even introduce problems. I have the same thinking with multiple indexes on the same column or composite key.

    I have a couple of systems that were developed years ago. I'm trying to clean them up.

    Thanks.

  • January 6, 2011 at 8:32 am

    #1270135

    Is there a question here?

    John

  • January 6, 2011 at 8:37 am

    #1270143

    Is there a known value to redundant FKs would be the question?

  • January 6, 2011 at 8:39 am

    #1270146

    I don't completely understand. You're saying that if you ran a query against sys.foreign_keys and sys.foreign_key_columns that you would not see data returned if there are duplicate keys? That's not true, so something else must be going on.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 6, 2011 at 8:47 am

    #1270150

    Sorry about that. I'll make this more concrete. Given a parent table A and a child table B, I can see multiple FKs defined for B referencing A on the same column.

    So I have a constraint called FK1 on child table B column 1 referencing table A's prime key.

    I can see 5 other constraints called FK2 through FK6 on the same column relationship.

    I think that this does not provide any value. Your thoughts?

    Thanks.

  • January 6, 2011 at 8:55 am

    #1270152

    r.pe (1/6/2011)

    Sorry about that. I'll make this more concrete. Given a parent table A and a child table B, I can see multiple FKs defined for B referencing A on the same column.

    So I have a constraint called FK1 on child table B column 1 referencing table A's prime key.

    I can see 5 other constraints called FK2 through FK6 on the same column relationship.

    I think that this does not provide any value. Your thoughts?

    Thanks.

    Oh, that makes a lot more sense. Thanks.

    Only if those other FK's are mapping to other columns in table B. In other words, Table A & Table B can be related through Col1 on Table And Col1, Col2, Col3 on Table B, with different values in each of the other columns on Table B, so multiple constraints makes sense. It's actually a pretty standard design method.

    On the other hand, if it's Col1 to Col1, five times, no, that's useless, unnecessary and probably slows things down.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 6, 2011 at 9:02 am

    #1270159

    Grant Fritchey (1/6/2011)

    On the other hand, if it's Col1 to Col1, five times, no, that's useless, unnecessary and probably slows things down.

    Yep, each FK would have to be validated to ensure that there are no conflicts. In this case, it's duplicated effort 5 times over.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 6, 2011 at 9:05 am

    #1270161

    Thanks for your input guys!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply