Re: altering a column's collation leaves an invalid foreign key

On 08.06.24 06:14, jian he wrote:
> if FK is nondeterministic, then it looks PK more like FK.
> the following example, one FK row is referenced by two PK rows.
>
> DROP TABLE IF EXISTS fktable, pktable;
> CREATE TABLE pktable (x text COLLATE "C" PRIMARY KEY);
> CREATE TABLE fktable (x text COLLATE ignore_accent_case REFERENCES
> pktable on update cascade on delete cascade);
> INSERT INTO pktable VALUES ('A'), ('Å');
> INSERT INTO fktable VALUES ('A');

Yes, this is a problem. The RI checks are done with the collation of
the primary key.

The comment at ri_GenerateQualCollation() says "the SQL standard
specifies that RI comparisons should use the referenced column's
collation". But this is not what it says in my current copy.

... [ digs around ISO archives ] ...

Yes, this was changed in SQL:2016 to require the collation on the PK
side and the FK side to match at constraint creation time. The argument
made is exactly the same we have here. This was actually already the
rule in SQL:1999 but was then relaxed in SQL:2003 and then changed back
because it was a mistake.

We probably don't need to enforce this for deterministic collations,
which would preserve some backward compatibility.

I'll think some more about what steps to take to solve this and what to
do about back branches etc.