CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
Jan Minárik
Jan Minárik

Reputation: 3267

How to implement a disjoint contraint on 1:1 relation in PostgreSQL?

I need to implement a specialization of an entity, using a 1-to-1 relation, let's say something like this:

create table vehicles (
  vehicle_id int primary key,
  ...
)

create table cars (
  car_id int primary key,
  vehicle_id int references vehicles(vehicle_id),
  ...
)

create table bikes (
  bike_id int primary key,
  bike_id int references vehicles(vehicle_id),
  ...
)

Now, I would like to enforce a disjoint contraint, to make sure a vehicle can be either a car or a bike, maybe neither, but never both. As I understand, it is not easy to achieve via RDBM itself, so I decided to validate this in the application (Ruby on Rails). In this case, I think it would be easier to validate if I do the relations the other way around, as it doesn't need any aditional selects and locks during insert:

create table vehicles (
  vehicle_id int primary key,
  car_id int references cars(car_id),
  bike_id int references bikes(bike_id),
  ...
)

create table cars (
  car_id int primary key,
   ...
)

create table bikes (
  bike_id int primary key,
  ...
)

Now, none of the disjoint constraints implementations I have found uses this way. Does it have any disadvantages that I can't see?

Upvotes: 0

Views: 632

Answers (1)

Rodolphe
Rodolphe

Reputation: 858

Just add a CONSTRAINT CHECK, something like this should answer

ALTER TABLE ONLY vehicles
    ADD CONSTRAINT cars_or_bikes  CHECK (car_id IS NULL OR bike_id IS NULL);

Upvotes: 1

Related Questions