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 t1 ( a int, b int, t1 int, PRIMARY KEY (a, b) )
But why not use a generated column instead?
CREATE TABLE t2 ( a int, b int NOT NULL DEFAULT 1 CHECK (b = 1), 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:
CREATE TABLE t2 ( a int, b int GENERATED ALWAYS AS (1) STORED, t2 int, FOREIGN KEY (a, b) REFERENCES t1 )
Produces the expected result. We can only insert (b = 1) into t2:
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
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:
- SQL Server
6 thoughts on “Having “constant” columns in foreign keys”
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.
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):
Anyway, why not recommend it to the original Tweet? https://twitter.com/connolly_s/status/1303957373107818497
It is an interesting idea of using generated column in that context :)
As for the
DEFAULT/CHECKconstraint tandem, I would add
NOT NULLas well. It current form it allows for NULL.
I agree, this was my intention. Although, from a philosophical point of view, NULLs might just be acceptable?
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?
Thanks for your suggestion. That would be very cool indeed. Supporting standard SQL
[ GENERATED ALWAYS ] ASexpressions in DDL, as well as other types of “readonly” columns will be supported soon. It’s a high priority for the next releases: https://github.com/jOOQ/jOOQ/issues/9864