What's the easiest method to emulate Tagged union in databases? I am speaking about something similar to this:

create table t1 {
  vehicle_id INTEGER NOT NULL REFERENCES car(id) OR motor(id) -- not valid
  ...
}

where vehicle_id could be id in vehicle table OR motor table, also it knows which.

(think that motor and vehicle tables do not have anything in common0

Many people make use of a design known as Polymorphic Associations to get this done, permitting vehicle_id to have a value that is available in both car or motor tables. Adding a vehicle_type that names the table that the given row in t1 references.

Unfortunately that you simply can't declare a genuine SQL foreign key constraint should you choose this. There is no support in SQL for any foreign key which has multiple reference targets. You will find other issues, too, but the possible lack of referential integrity has already been an offer-breaker.

A much better design would be to borrow an idea from OO style of a common supertype of both car and motor:

CREATE TABLE Identifiable (
 id SERIAL PRIMARY KEY
);

Make t1 reference this super-type table:

CREATE TABLE t1 (
  vehicle_id INTEGER NOT NULL,
  FOREIGN KEY (vehicle_id) REFERENCES identifiable(id)
  ...
);

As well as result in the sub-types reference their parent supertype. Observe that the main key from the sub-types is not auto-incrementing. Parents supertype takes proper care of allocating a brand new id value, and also the children only reference that value.

CREATE TABLE car (
  id INTEGER NOT NULL,
  FOREIGN KEY (id) REFERENCES identifiable(id)
  ...
);

CREATE TABLE motor (
  id INTEGER NOT NULL,
  FOREIGN KEY (id) REFERENCES identifiable(id)
  ...
);

You can now have true referential integrity, but additionally support multiple subtype tables using their own characteristics.


The solution by @Quassnoi also shows a means to enforce disjoint subtypes. That's, you need to prevent both car and motor from referencing exactly the same row within their parent supertype table. After I do that, I personally use just one-column primary key for Identifiable.id but additionally declare a UNIQUE key over Identifiable.(id, type). The foreign secrets in car and motor can reference the 2-column unique key rather than the main key.

CREATE TABLE vehicle (type INT NOT NULL, id INT NOT NULL,
             PRIMARY KEY (type, id)
)

CREATE TABLE car (type INT NOT NULL DEFAULT 1, id INT NOT NULL PRIMARY KEY,
             CHECK(type = 1),
             FOREIGN KEY (type, id) REFERENCES vehicle
)

CREATE TABLE motorcycle (type INT NOT NULL DEFAULT 2, id INT NOT NULL PRIMARY KEY,
             CHECK(type = 2),
             FOREIGN KEY (type, id) REFERENCES vehicle
)

CREATE TABLE t1 (
  ...
  vehicle_type INT NOT NULL,
  vehicle_id INT NOT NULL,
  FOREIGN KEY (vehicle_type, vehicle_id) REFERENCES vehicle
  ...
)

I believe you can model this type of reference by utilizing table inheritance in PostgreSQL.

If you will need to know in which a row originates from inside a Query, you could utilize an easy UNION ALL statment like (this possibility is not related to table inheritance):

SELECT car.*, 'car' table_name
UNION ALL
SELECT motor.*, 'motor' table_name