Multiple reference in a single field

  • Hi Dudes,

    I need to have a foreign key field that should refer from two tables. for an example,

    table_a

    --------

    1 a_id primary key,

    2 name

    table_b

    --------

    1 b_id primary key,

    2 name

    table_c

    -------

    1 c_id

    2 name

    3 key_id (i.e, it should be foreign key from either table_a or table_b. it may have value of either table_a's id or table_b's id

    Can any one of u help me to get this script to create table.

    Thanks

    Saga

  • How will you determine which lookup table the foreign key value is from, for each row of table c?

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • hi,

    I think its not possible to do......

    Try to do the following one...

    For this U should add another column in TableC.

    Key_IDa is foreign key and references to Table A PK.

    Key_IDb is foreign key and references to Table B PK.

    Why dont you check with the Wizard options availed from SSMS.

    For relationships btwn tables ,Creating Foreign Keys........

    ๐Ÿ™‚

    Thanks!

    Sasidhar Pulivarthi

  • Sasidhar is correct.

    FKs are there to enforce referential integrity rules. No referential integrity rules takes into consideration one child row with two alternative parents.

    If this is a business spec it has to be manually enforced.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • You cannot enforce this with static RI (referential integrity) without the use of an artificial "helper" table, which is a bit of a mess to setup and maintian and not recommended. The recommended alternative is to use dynamic RI, (which almost always means triggers) to enforce this constraint.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

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