SQL Server Performance

Primary key (Composite) & Foreign key

Discussion in 'SQL Server 2008 General DBA Questions' started by rohaandba, Jan 20, 2011.

  1. rohaandba New Member

    I have a 'Root' table with 8 columns. First 5 columns are to be declared as a Primary key(Composite).
    And I have 10 to 15 tables, each with a Foriegn key referencing to this 'Root' table's Composite Primary key.
    I am not able to do that as it says that the number of columns in the Primary key doesn't match with the Columns of the Foriegn key.
    Can I solve this scenario or do I need to change the structure of the tables
    Please help me
  2. FrankKalis Moderator

    Welcome to the forum!
    Given this information I would argue use a single column surrogate PRIMARY KEY and put a UNIQUE constraint on the 5 column that now make up the PRIMARY KEY. Then all your child tables would only need to reference this single column PK.
    It surely will work to reference a 5 column PK, so I guess there is something wrong in your script, but using a single column PK has several advantage in terms of performance, storage efficiency, etc...
  3. RamJaddu Member

  4. rohaandba New Member

    Thank you,
    Can I have the T-SQL syntax for the surrogate key please ?
  5. Luis Martin Moderator

    Check:
    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be/
  6. satya Moderator

  7. rohaandba New Member

    Actually I would explain in a better way

    I have a 'Root' table with 8 columns.
    First 5 columns in 'Root table ' are used in all 10 child tables of that root table.
    I first wanted to make the first 5 columns of the 'root' table as Composite Primary key ,

    But the data thats going to come into the 5 columns will not be unique (like a column may have same hospital name with different patient numbers starting from 1,2,3----n in the patient column, When the hospital name changes in the Hospital column then the patient number again starts from 1,2,3----n ).

    Note: Both the 'Hospiatal Name' & 'Patient number' are 2 columns among the 5 columns of the root table.
    So as per my requirement ,Client wants to use all the first 5 columns of the root table in other 10 child tables also.
    I am not able to do that as per the nomalization criteria.
    Can anyone please suggest me how to design.
  8. FrankKalis Moderator

    I'm not entirely sure I understand what you say, but if the 5 columns are not guaranteed to be unique they are no candidate for the PRIMARY KEY. Simple as that. So you may have to consider adding more attributes to the key or consider a different strategy.
    But what I don't understand is when you say: "Client wants to use all the first 5 columns...." Since when should the client have any influence on the physical implementation? He may have a saying during logical design, but the implementation should be up to you. It is very seldom that clients fully understand the implications of their requirements. So this would be your job then. Guess whom he will come back to when you implement it the way he wants and then some time later on is facing performance issues, etc...?
    For most cases I would really consider a surrogate PRIMARY KEY instead of a 5+ column composite key.

Share This Page