locked
Foreign Key from two primary tables RRS feed

  • Question

  • I have two primary key tables as:

    Individual

    ========================================
    IndividualID   IndividualName   Address
    ========================================
    01                rahul                  Delhi
    02                 rohit                  ----

    primary key is IndividualID


    Corporate

    ========================================
    CorporateID   CorporateName    Address
    ========================================
    05                 abc                    bcd
    06                  xyz                   123 b

    primary key is CorporateID

    I have one foreign key table as:

    Industry
    ============================================
    IndustryID ClientID IndustryType
    ============================================
    01            01           Type1
    02            05           Type2
    03            02           Type3

    I want foreign key constraint on clientID for both

    primary key table Individual(IndividualID) and

    corporate(corporateID).

    That is in Industry table, only those

    IDs(ClientID) can add which are existed into both

    primary key table i.e. IndividualID in Individual table and CorporateID in Corporate table.


    i.e. can i add foreign key constrant from two tables id fields.


    Rahul Maurya
    Friday, March 18, 2011 11:49 AM

Answers

  • Allright, I understand your question now, a client can be either a Individual or a Corporate.

    This stackoverflow thread explains how you should solve this issue by designing your database correctly:

    http://stackoverflow.com/questions/668921/foreign-key-refering-to-primary-keys-across-multiple-tables

    Essentially you need to create a base table that contains basic information about Individual and Corporate.
    Then the 2 tables for Individual and Corporate who "inherit" from that base table. It's a bit like classes and inheritance in object-oriented programming.

    The foreign key in the industry table will point to the base table, as it will contain all the rows for Individuals and Corporates.

    • Proposed as answer by Koen VerbeeckMVP Wednesday, March 23, 2011 10:37 AM
    • Marked as answer by Jerry Nee Sunday, April 10, 2011 2:45 AM
    Wednesday, March 23, 2011 10:37 AM

All replies

  • In your foreign key table, Industry, you don't have a column for CorporateID. So how do you expect SQL Server to put a foreign key on it?
    You should add the column CorporateID to Industry, and then add 2 foreign keys: one for ClientID that points to Individual(IndividualID) and one for CorporateID that points to Corporate(CorporateID).

    Friday, March 18, 2011 2:26 PM
  • Sir,

    I hava column ClientID in Industry. It will store both  columns values of Individual(IndividualID) and Corporate(CorporateID).

    So, I want one or two foreign for ClientID that point to both IndividualID as well as CorporateID of the tables Individual and Corporate respectively.


    Rahul Maurya
    Monday, March 21, 2011 9:43 AM
  • Allright, I understand your question now, a client can be either a Individual or a Corporate.

    This stackoverflow thread explains how you should solve this issue by designing your database correctly:

    http://stackoverflow.com/questions/668921/foreign-key-refering-to-primary-keys-across-multiple-tables

    Essentially you need to create a base table that contains basic information about Individual and Corporate.
    Then the 2 tables for Individual and Corporate who "inherit" from that base table. It's a bit like classes and inheritance in object-oriented programming.

    The foreign key in the industry table will point to the base table, as it will contain all the rows for Individuals and Corporates.

    • Proposed as answer by Koen VerbeeckMVP Wednesday, March 23, 2011 10:37 AM
    • Marked as answer by Jerry Nee Sunday, April 10, 2011 2:45 AM
    Wednesday, March 23, 2011 10:37 AM
  • Thanks.
    Rahul Maurya
    Tuesday, April 5, 2011 4:58 AM