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

On Thu, Nov 7, 2024 at 8:15 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> Apparently this is intentional. It's the difference between RESTRICT
> and NO ACTION. In ri_restrict(), there is a comment:
>
> /*
> * If another PK row now exists providing the old key values, we should
> * not do anything. However, this check should only be made in the NO
> * ACTION case; in RESTRICT cases we don't wish to allow another
> row to be
> * substituted.
> */
>
> In any case, this patch does not change this behavior. It exists in old
> versions as well.
>

https://stackoverflow.com/questions/14921668/difference-between-restrict-and-no-action
mentioned about the difference between "no action" and "restrict".

RI_FKey_restrict_upd comments also says:

* The SQL standard intends that this referential action occur exactly when
* the update is performed, rather than after. This appears to be
* the only difference between "NO ACTION" and "RESTRICT". In Postgres
* we still implement this as an AFTER trigger, but it's non-deferrable.

DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
CREATE TABLE fktable (x text collate case_insensitive REFERENCES
pktable on update restrict on delete restrict);
INSERT INTO pktable VALUES ('A'), ('Å');
INSERT INTO fktable VALUES ('a');
update pktable set x = 'a' where x = 'A';
----------------------------------------------------------
DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
CREATE TABLE fktable (x text collate case_insensitive REFERENCES
pktable on update no action on delete no action);
INSERT INTO pktable VALUES ('A'), ('Å');
INSERT INTO fktable VALUES ('a');
update pktable set x = 'a' where x = 'A';

In the above two cases, the last queries behavior difference does not
look like "no action" vs "restrict" mentioned in the doc (create_table.sgml),
or the above stackoverflow link.
so this part, i am still confused.

-----------------------------<<>>>-----------------------------
CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
INSERT INTO pktable VALUES ('A'), ('Å'), ('H');
INSERT INTO fktable VALUES ('a');

fktable foreign key variants:
collate case_insensitive REFERENCES pktable on update set default on
delete set default
collate case_insensitive REFERENCES pktable on update set null on
delete set null
collate case_insensitive REFERENCES pktable on update cascade on delete cascade

`update pktable set x = 'a' where x = 'A';`
will act as if the column "x" value has changed.
so it will do the action to fktable.

following the same logic,
maybe we should let "on update no action on delete no action"
fail for the following case:

DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
CREATE TABLE fktable (x text collate case_insensitive REFERENCES
pktable on update no action on delete no action);
INSERT INTO pktable VALUES ('A'), ('Å');
INSERT INTO fktable VALUES ('a');

---expect it to fail. since column "x " value changed and is still
being referenced.
update pktable set x = 'a' where x = 'A';
-----------------------------<<>>>-----------------------------

I added a "on update cascade, on delete cascade" tests on collate.icu.utf8.sql
for both foreign key and primary key are nondeterministic collation.