"how to emulate tagged union in a database?" Code Answer

some people use a design called polymorphic associations to do this, allowing vehicle_id to contain a value that exists either in car or motor tables. then add a vehicle_type that names the table which the given row in t1 references.

the trouble is that you can't declare a real sql foreign key constraint if you do this. there's no support in sql for a foreign key that has multiple reference targets. there are other problems, too, but the lack of referential integrity is already a deal-breaker.

a better design is to borrow a concept from oo design of a common supertype of both car and motor:

create table identifiable (
 id serial primary key
);

then make t1 reference this super-type table:

create table t1 (
  vehicle_id integer not null,
  foreign key (vehicle_id) references identifiable(id)
  ...
);

and also make the sub-types reference their parent supertype. note that the primary key of the sub-types is not auto-incrementing. the parent supertype takes care of allocating a new id value, and 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)
  ...
);

now you can have true referential integrity, but also support multiple subtype tables with their own attributes.

the answer by @quassnoi also shows a method to enforce disjoint subtypes. that is, you want to prevent both car and motor from referencing the same row in their parent supertype table. when i do this, i use a single-column primary key for identifiable.id but also declare a unique key over identifiable.(id, type). the foreign keys in car and motor can reference the two-column unique key instead of the primary key.