locked
primary foreign key referencing RRS feed

  • Question

  • Hi All,

      Can part of a composite primary key  in a table reference another part of a composite primary key on another table? Is that bad design?

    please advise...

    Friday, October 21, 2011 11:51 AM

Answers

  • >This means that a primary key can be a primary and a foreign key at the same time in the same table? And that is completely okay, correct?

    Your original question was about using a part of a primary key as a
    foreign key. That is completely okay, and you'll typically see it in
    Order / OrderDetail patterns

    This question is about using the whole primary key as a foreign key.
    That is okay as well, though less common. You will typically see this
    in a subtype/supertype hierarchy. (Classroom example: table "Persons"
    with columns like name, length, birthday, gender; subtable "Men" with
    columns like facial hair, military service [okay, that part of the
    example is now outdated in most countries]).


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by Stephanie Lv Friday, October 28, 2011 6:59 AM
    Sunday, October 23, 2011 8:02 PM

All replies

  • On Fri, 21 Oct 2011 11:51:25 +0000, N.Afache wrote:

    Hi All,

      Can part of a composite primary key  in a table reference another part of a composite primary key on another table? Is that bad design?

    please advise...

    Hi N.Afache,

    No, and yes.

    Part of a composite primary key can reference a complete primary
    key in another table, but not part of it.

    For a foreign key, it is required that the referenced column(s) is
    declared as either a primary key constraint, a unique constraint, or a
    unique index. While it is technically possible to declare a unique
    constraint on a column that is part of a composite primary key, it is
    not good design.

    Maybe you have encountered a situation that is beyond what I have ever
    seen and where this would indeed be a good design, but without knowing
    the specifics, I'm oing to say it's not.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Warwick Rudd Saturday, October 22, 2011 12:11 PM
    Friday, October 21, 2011 7:30 PM
  • Lets say that part of the composite primary key is referencing a complete primary key. In your view that did not sound like a good design.

    Can we conclude from this discussion that it is not a good approach to have a referencing column be a primary key or part of a primary key?

    Saturday, October 22, 2011 7:45 AM
  • On Sat, 22 Oct 2011 07:45:03 +0000, N.Afache wrote:

    Lets say that part of the composite primary key is referencing a complete primary key. In your view that did not sound like a good design.

    Can we conclude from this discussion that it is not a good approach to have a referencing column be a primary key or part of a primary key?

    Hi N.Afache,

    No, you must have misunderstood me. A part of a primary key
    referencing a complete primary key in another table is completely
    okay. It is in fact very common (e.g. the almost standard Orders /
    OrderLines example).


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Angelo_Paapa Wednesday, September 19, 2012 5:26 PM
    Saturday, October 22, 2011 9:22 AM
  • This means that a primary key can be a primary and a foreign key at the same time in the same table? And that is completely okay, correct?
    Sunday, October 23, 2011 11:10 AM
  • Yes,something like this

    create table t1 (c int primary key)

    create table t2 (c int primary key foreign key references t1(c))


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 23, 2011 11:25 AM
  • Apart from the fact that I can do it in sql server, I am asking this question from a database design perspective. Good versus bad design to have the same key in the same table as primary and foreign at the same time?
    • Edited by N.Afache Sunday, October 23, 2011 11:35 AM
    Sunday, October 23, 2011 11:33 AM
  • It's a common way to code hierarchy. It's an OK design, although may be not the best.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, October 23, 2011 2:03 PM
    Answerer
  • >This means that a primary key can be a primary and a foreign key at the same time in the same table? And that is completely okay, correct?

    Your original question was about using a part of a primary key as a
    foreign key. That is completely okay, and you'll typically see it in
    Order / OrderDetail patterns

    This question is about using the whole primary key as a foreign key.
    That is okay as well, though less common. You will typically see this
    in a subtype/supertype hierarchy. (Classroom example: table "Persons"
    with columns like name, length, birthday, gender; subtable "Men" with
    columns like facial hair, military service [okay, that part of the
    example is now outdated in most countries]).


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by Stephanie Lv Friday, October 28, 2011 6:59 AM
    Sunday, October 23, 2011 8:02 PM
  • Nice job explaining. Those that want to understand how logical super and sub types and even many-to-many relationships in a logical model work and how that converts to a physical model is a unique understanding from a developers view. That is a way to hone your skills as a developer. Syntax is so boring. :) The old days they tried calling it software engineers, never liked that term.

    Sunday, October 30, 2011 7:32 AM