Column with both primary and foreign key constraints

  • Data Rat

    SSChasing Mays

    Points: 617

    Hi Friends,

    I am a Newbie to T-SQL Database Development. My question is I notice that in Adventureworks2012 Database there is a primary key column named BusinessEnitityID in a table named HumanResources.Employee that also has a foreign Key constraint on the same column, and I was just wondering why is this? Also I notice that in Adventureworks2012 there are tables  that have multiple columns with foreign key constraints, and I was wondering what is the purpose for having multiple foreign key constrains on a table? Thanks !

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714600

    A few things. First, Adventureworks is built as a sample to show off features for Microsoft. It's not necessarily a great database design.

    A PK is for uniqueness in a table.
    A FK is to ensure parent/child records exist without orphans.

    Both  can be important in situations. There are times that I have a FK to another table that is part  of a PK on this table.

    When you say multiple columns, you're not being clear. To you mean multiple columns make up a FK or that multiple columns have separate FKs to different other tables? Either are fine.

  • Data Rat

    SSChasing Mays

    Points: 617

    Thank You for your help. I understand why a table can have multiple foreign keys. But you commented about sometimes you will have both a foreign key and primary key constraint one a single column. If you don't mind could you please share with me particular instances in your design why this practice would be necessary. Just using my own intuition, is this to establish the uniqueness of a foreign key constraint in a many to many relationship? And is this practice also used to avoid having to use a junction table? Again, Thanks for your help.

  • ZZartin

    SSC-Dedicated

    Points: 30310

    Well the reason you might have FK columns as part of the primary key is if they are part of what makes the record unique, it also enforces not null etc...  A common case might be something like an order item table, with the primary key of a line item id and a FK to the order table.  And no this is not a method to avoid having to use a junction(or intersection) table, those are used to support a different type of relationship, something you might see in that case would be a primary key on the junction table made up of two FK's.

  • Data Rat

    SSChasing Mays

    Points: 617

    In the example scenario that you used would you combine the primary  key and the foreign key so that your orders would be unique? Because I thought that would be the purpose of having a primary key on the orders table?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714600

    The only place I can think of off the top of my head for the PK and FK to be the same column (or columns) is a vertical partition.

Viewing 6 posts - 1 through 6 (of 6 total)

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