Home Forums SQL Server 2008 T-SQL (SS2K8) FK referencing multiple tables RE: FK referencing multiple tables

  • September 26, 2012 at 4:51 am

    #1542305

    PiMané (9/20/2012)

    Hi,

    Is it possible to have a column on table A that references table B or table C depending on another column on table A?

    For example:

    EntityAddresses (EntityAddressId, EntityType, EntityID, Address)

    Customers (CustomerID, ....)

    Suppliers (SupplierID, ....)

    Can EntityID be a FK to both CustomerID and SupplierID but only check the value in CustomerID if EntityType is 0 and SupplierID if EntityType is 1?

    Thanks,

    Pedro

    It's not that uncommon to have a business entity which is both a supplier and a customer to your business. With your relationships modelled in this direction, you'd have to duplicate an address.

    If address is an attribute of customer (or supplier), you don't. You also don't have this problem of a key having more than one meaning.

    “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