REFERENCES two different columns in two different tables

Question

In a Postgresql database we have a table like below:

=# SELECT * FROM toy_cars;
  serial_no |     name
------------+---------------
 199ER276FN | Snow Doctor
 8BE0F79A3R | Flatbed Truck
 D76185CE8G | Sand Speeder

=# SELECT * FROM toy_trains;
    serial_no    |    name
-----------------+-------------
 BMXH5R4T8K7KELD | Howler T140
 B1Q1JJDQW9LQN0G | Quakester
 8HO9240TO6RNNQ9 | Medusa 90

=# SELECT * FROM items_for_sale;
    serial_no    |   in_stock
-----------------+---------------
      199ER276FN | t
 BMXH5R4T8K7KELD | t
 B1Q1JJDQW9LQN0G | f
      8BE0F79A3R | f
 8HO9240TO6RNNQ9 | t
      D76185CE8G | f

Note:

• Every serial_no column is the primary key of that table and in_stock is a boolean.

• serial_no in the toy_cars table has a regex CHECK restraint to allow 10 characters only.

• serial_no in the toy_trains table has a regex CHECK restraint to allow 15 characters only.

• serial_no in the items_for_sale table is the serial of either the toy cars or trains, and has a regex CHECK restraint to allow 10 or 15 characters only.

• All serial_no columns have the UNIQUE restraint.

We want to add a REFERENCES check to serial_no in the items_for_sale table to make sure that the entered serial is either present in the toy_cars table OR the toy_trains table.

So, if I were to try INSERT INTO items_for_sale VALUES('KYVGK0DBYXPMWW8','f'); this would fail because that serial is not present in either toy_cars or toy_trains.

How can this be done? We prefer to use one table (like it's structured now).

Answer 1

The problem here is that you want to check presence of a key in two (other) tables, and you cannot enforce that using foreign key constraints on a single column. Postgres allows you to create CHECK expressions for custom checks, but as the manual says:

PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. (...) If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express cross-row and cross-table restrictions.

If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained consistency guarantee, a custom trigger can be used to implement that.

See this very related question for different solutions.

Some databases (MS SQL Server) allow you to use a function in CHECK expressions. That would be optimal, but Postgres does not allow that syntax.

For PostgreSQL, you need to create a TRIGGER that will execute when something is inserted into items_for_sale. That, on the other hand, does allow functions or cross-table checks.

It would look something like this:

CREATE TRIGGER check_serial_present
    BEFORE INSERT ON items_for_sale
    FOR EACH ROW
    EXECUTE FUNCTION assert_presence_of_serial(); -- implement this function

The other linked question also mentions a quite elegant way of achieving this without triggers:

A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints

I quite like this as it's conceptually very simple and easy to grasp. I think it would just involve these steps:

1. ALTER TABLE to add two columns: toy_car_serial and toy_trains_serial, both NULLABLE, but with FOREIGN KEY constraints on the mentioned tables.
2. Make sure that any INSERT will insert a serial into both serial_no and toy_car_serial OR serial_no and toy_car_serial.
3. Add CHECK( toy_car_serial = serial_no OR toy_trains_serial = serial_no).

I think two redundant rows and a slight modification to your inserts is a lot less involved than the alternative.