How to enforce that two (PK same FK) columns from two different tables are disjoint

Question

Consider the following example

• Vehicle is my superclass
• Bike and Car are my subclasses

CREATE TABLE Vehicle (

    Vehicle_id        INT   PRIMARY KEY
    ...

);

CREATE TABLE Bike (

    Vehicle_id       INT   PRIMARY KEY REFERENCES Vehicle(Vehicle_id)
    ...

);

CREATE TABLE Car (

    Vehicle_id       INT   PRIMARY KEY REFERENCES Vehicle(Vehicle_id)
    ...

);

• Vehicle doesn't have to be a Bike or a Car but it can't be both a Bike and a Car
• I've been trying something like this (How do I reference Bike in ??)

ALTER TABLE ONLY Car
   ADD CONSTRAINT not_in_bike CHECK (??)

• Alternatively, can I do something like this

ALTER TABLE Car AND Bike
    ADD CONSTRAINT Car_or_Bike CHECK (Car.Vehicle_id <> Bike.Vehicle_id)

Thank you!

Answer 1

There are two possibilities:

1. Store all entities in a single table and use a column (type or so) to determine which type a certain row is.

2. Use the layout you have now, but add a column type to all three tables. Add that column to the primary key or create a unique constraint on (vehicle_id, type).

   Define the foreign keys to include type and make sure they have the correct value using a CHECK constraint:

   ALTER TABLE bike ADD FOREIGN KEY (vehicle_id, vehicle_type) REFERENCES vehicle;
   
   ALTER TABLE bike ADD CHECK (vehicle_type = 'bike');