While teaching a database basics course, a student asked about Foreign Keys whose data type does not match the data type of the thing (e.g. Primary Key) they are referencing.
For example, all numbers that can be stored in an
INTEGER column can be expressed as
TEXT, and so a
TEXT column's data may be used to reference data in an
INTEGER column, as long as the appropriate typecasts/conversions are applied.
We use PostgreSQL in teaching (because of its excellent documentation, among other things), so we went and had a look. Lo and behold, the "simplified" chapter about Foreign Keys told us:
Of course, the number and type of the constrained columns need to match the number and type of the referenced columns.
Further research in the "feature complete" section about CREATE TABLE did not explicitly mention data types, though. This part only talks about values.
We tried various combinations of data types, some more convincing (like the
TEXT variant from above) than others. The DBMS was not convinced and replied with
42804: incompatible types.
So far, so good. Imagine our utter astonishment when we found out that PostgreSQLs various Integer-types in fact do work.
They even take the sign correctly into account, which means they are not just matching up bits.
Of course there is a direction this should work in: Having an
INTEGER column that is referenced by a
BIGINT column always works, since everything that fits into the referenced column also fits into the referencing column.
Surprisingly, PostgreSQL allows the other direction (with
SMALLINT in this example):
CREATE TABLE this_should_not_work ( this_should_not_work_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, data TEXT ); CREATE TABLE this_should_not_work_detail ( detail_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, detail_data TEXT, -- This Foreign Key references a column of a different type, which should not be possible fk_this_should_not_work_id SMALLINT REFERENCES this_should_not_work (this_should_not_work_id) );
Here is an executable version of the above problem: db-fiddle
The db-fiddle also has sequence options and
INSERT-Statements that trigger a failure on the second insertion.
Note that the Documentation and the db-fiddle linked above is for PostgreSQL 13, but the problem(?) can be reproduced on PostgreSQL 14 as well.
I am aware that foreign key type mismatch is a database design problem.
The question is, why does PostgreSQL point out the obvious cases (
TEXT), but not the more subtle ones (
PS: A lot of havok can be caused when this is abused in combination with
ON UPDATE CASCADE, since the update on the referenced table fails because of the data not fitting into the referencing table - this makes for a rather "creative" error message.