Post reply

Foreign Key constraint between more than 2 tables

  • April 16, 2009 at 2:17 pm

    #384380

    Hi,

    May be there is a simple solution here but I am not seeing it... so any comments are appreciated.

    We have 3 tables: 2 tables for store messages and 1 to track the status (..of those messages in 2 tables)

    Message Tables

    Table_1

    Table_2

    Status Table

    Status

    Structure of both the Message tables is almost the same

    Id (Identity Column),

    Message_Identifier (Business ID)

    Message VARCHAR

    ...

    ...

    Structure of status table is:

    ID

    Message_Identifier

    Status

    Error_Description

    ...

    ...

    Message tables are connected to status table with Message_Identifier (Business ID).

    Now, is there a way to create foreign key relation from Status table to both the message tables.

    thanks,

    _Uday

    SqlTimes Blog on Sql Server

  • April 16, 2009 at 2:22 pm

    #979233

    Not really. Foreign keys references one other table.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 16, 2009 at 2:28 pm

    #979237

    Thank you GSquared. I was starting to have the same opinion.

    thanks,

    _Ub

    SqlTimes Blog on Sql Server

  • April 17, 2009 at 6:51 am

    #979607

    So you want to insure that the rows in the Status table have a valid Message_Identifier from one of the 2 message tables? While not ideal, this situation is one where a trigger can be used to enforce referential integrity. You might also be able to create a UDF that you could use in a check constraint on the Status.Message_Identifier column that verifies that it is a valid Message_Identifier.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • April 17, 2009 at 7:51 am

    #979678

    UDF...mmm.... did not know that was possible. I'll try it.

    Update: I wrote a trigger to accomplish this, but I'll try UDF, just to test.

    SqlTimes Blog on Sql Server

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

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