CODEWITHSUNDEEP
sqlsql-serversql-server-2008constraintsforeign-key-relationship
Dylan Slabbinck
Dylan Slabbinck

Reputation: 854

Foreign Key Reference 2 columns with OR Construction

I need to do some migrations on our database and I have a problem.

A column (AirportCodeDeparture) in my table can refer to either AirportCode or AirportMetropolitanCode from the Airport Table. Note: this does not mean that a AirportCodeDeparture is a AirportCode AND a AirportMetropolitanCode, no, this is the OR part of my problem.

db scheme

So I try to create a table that corresponds to this situation but I don't quit know how to implement the OR part of the foreign key references.

This is what I have now:

CREATE TABLE FlightAllotment
(
    FlightAllotmentId smallint not null IDENTITY(1,1), CONSTRAINT PK_FlightAllotmentId PRIMARY KEY (FlightAllotmentId),

    AirportCodeDeparture char(5) not null, CONSTRAINT FK_FlightAllotment_AirportCodeDeparture FOREIGN KEY (AirportCodeDeparture) REFERENCES Airport(AirportCode) OR Airport(AirportMetropolitanCode),
)

This part: REFERENCES Airport(AirportCode) OR Airport(AirportMetropolitanCode)

I already tried 

REFERENCES Airport(AirportCode) || Airport(AirportMetropolitanCode)

REFERENCES Airport(AirportCode || AirportMetropolitanCode)

REFERENCES Airport(AirportCode OR AirportMetropolitanCode)

Upvotes: 0

Views: 115

Answers (1)

simon at rcl
simon at rcl

Reputation: 7344

You can't do that, I'm afraid. The target of an FK needs to be a unique key on the target table, and can only be one column as well. There is no OR allowed.

One way to do this is to an entry in Airport for the parent AirportMetropolitan and then refer to that entry, but I don't know how sensible that would be.

The other way is to have FlightAllotment.AirportDepartureCode be an FK to Airport but nullable so you don't have to supply it when you want to use an AirportMetropolitan code. You could also add FlightAllotment.AirportMetropolitanDepartureCode as an FK to AirportMetropolitan.AirportMetropolitanCode, also as a nullable column.

Those are your options I'm afraid.

Upvotes: 1

Related Questions