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:
Thanks 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------------------------------

 

[tlbroadbent]

Foreign Key: an attribute in a relation of a database that is the primary key of another relation in the same database.

You can't create an "either this or that" foreign key constraint on a column. The very definition of foreign key prevents that. A foreign key in a table relates to a primary key in one table. (SQL Server allows foreign keys to relate to unique columns that are not primary keys.)

In SQL Server, you can provide the kind of referential integrity checking you want through the use of triggers. You can also define relationships between tables in queries and views without having the relationships defined in the database schema.

In short, foreign keys are unnecessary to accomplish your purposes. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm