Conditional Foreign Key - can it be done???

[Guest_imported]

Hello Folks,

Well I've hit the wall on this one:

I'm wondering if one can designate a conditional foreign key that relates to one of many different tables depending on the "type" column in the foreign key's. My goal is to come up with some SQL code that will allow for this in a Create table statement. (By the way I'm using MS SQL-7 but I'm wondering if this can be done in general.)

I have two simple cases below that show illustrate what I'm trying to do:
Thank in advance, -JerryZZ


---------------------------------------------------------
Case 1: The foreign key relation is to a primary key

Table: Invoices
-Fields:
---InvoiceID ..... (primary key)
---BilleeID ...... (fkey to Manfacturers.ManfID IF BilleeType=M)
...................(fkey to Distibutors.DistID IF BilleeType=D)
---BilleeType .....(constraint = M or D)

Table: Manufacturers
-Fields:
---ManfID ........ (primary key)
---CompanyName.. (not null)

Table: Distributors
-Fields:
---DistID ........ (primary key)
---CompanyName .. (not null)



---------------------------------------------------------
Case 2: The foreign key relation is to a unique "not null" non-primary key

Table: InvoicesEmail
-Fields:
---InvoiceID ..... (primary key)
---EmailAddress .. (fkey to Manfacturers.EmailAddress IF BilleeType=M)
...................(fkey to Distibutors.EmailAddress IF BilleeType=D)
---BilleeType .....(constraint = M or D)

Table: Manufacturers
-Fields:
---ManfID ........ (primary key)
---EmailAddress .. (unique, not null)

Table: Distributors
-Fields:
---DistID ........ (primary key)
---EmailAddress .. (unique, not null)

---------------End-O-Message------------------------------

 

[carp]

No.
However, in Oracle (and I suspect most other RDBMS), you COULD write a trigger that would emulate this behavior for you.

 

[emre]

CREATE VIEW

SELECT * FROM TABLE1, TABLE2
WHERE ( YOUR 1st CONDITION)

UNION (ALL)

SELECT * FROM TALBE1, TABLE2
WHERE ( YOUR 2nd CONDITION)