Having “constant” columns in foreign keys

Posted on by lukaseder
I was asked a very interesting question on Twitter just now:
Can we have “constant” foreign key columns in (PostgreSQL) tables? Luckily, yes, we can. Using a nice standard feature that is “computed columns” or “generated columns” Sometimes, you cannot completely normalise your schema for whatever reason. There may be a case where you have a table with a composite primary key like this: 

CREATE TABLE t1 (
  a int,
  b int,
  t1 int,
  PRIMARY KEY (a, b)
)
And in a referencing table t2, you will always reference one of the primary key columns by value, say, 1. Of course, you could create a table t2 with a CHECK constraint ensuring b = 1: 

CREATE TABLE t2 (
  a int,
  b int NOT NULL DEFAULT 1 CHECK (b = 1),
  t2 int,
  FOREIGN KEY (a, b) REFERENCES t1
)
But why not use a generated column instead? 

CREATE TABLE t2 (
  a int,
  b int GENERATED ALWAYS AS (1) STORED,
  t2 int,
  FOREIGN KEY (a, b) REFERENCES t1
)
In my opinion, this is even more powerful. As of PostgreSQL 12, only STORED is supported (meaning the value is stored on disk), when in this case VIRTUAL would be even better (meaning the value is produced only when reading the row). Inserting some test data: 

INSERT INTO t1 (a, b, t1) 
VALUES(1, 1, 1), (1, 2, 2), (2, 1, 3);

INSERT INTO t2 (a, t2) 
VALUES (1, 11), (2, 12);

SELECT * 
FROM t1
NATURAL LEFT JOIN t2
Produces the expected result. We can only insert (b = 1) into t2: 
a|b|t1|t2|
-|-|--|--|
1|1| 1|11|
2|1| 3|12|
1|2| 2|  |
A nice trick to keep up one’s sleeve. Computed or generated columns are available in a variety of RDBMS, including at least:
  • Db2
  • MySQL
  • Oracle
  • PostgreSQL
  • SQL Server

Published by lukaseder

I made jOOQ

8 thoughts on “Having “constant” columns in foreign keys”

  1. This sounds a lot like ESR’s “Explain the goal, not the step” advice. The question likely means that what they have is one table of items with a column describing “type”, and then a table for each type that references an item but includes other information that varies with the type. Kudos for identifying that correctness is hard to enforce and coming up with a solution.

    But, in this specific case, and since they’re using postgres: it’s very likely that table inheritance would have been a much better solution, obviating the problems associated with the question altogether.

    Reply

    1. Table inheritance is a really weird feature and even the PostgreSQL documentation discourages its use. At least, if you’re reading between the lines of e.g. this (https://www.postgresql.org/docs/current/tutorial-inheritance.html):

      Although inheritance is frequently useful, it has not been integrated with unique constraints or foreign keys, which limits its usefulness.

      Anyway, why not recommend it to the original Tweet? https://twitter.com/connolly_s/status/1303957373107818497

      Reply

  3. Wouldn’t it be cool if jOOQ would support these “generated always” columns? Perhaps I’m missing something but for the example above: couldn’t the T2Record always return 1 for getB … and also omit it in the constructor?

    Reply

  4. The solution helps, but introduces some smell I don’t like. E.g. table T2 always shows a set of totally useless columns for each “constant” used. (when doing a select * or something).

    Imho the “real” solution would be to persuade the DB developers to allow constants in the FK definition, like

    CREATE TABLE t2 (
    a int,
    t2 int,
    FOREIGN KEY (a, 1) REFERENCES t1
    );

    This would help especially when working with discriminator values and “single table inheritance”.

    Either way – your proposal at least works, thanks for it !

    Reply

    1. Some RDBMS have computed columns (ideally virtual, as opposed to stored), so that would be the idiomatic solution along the lines of your suggestion.

      Reply

Leave a Reply