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

On Wed, Nov 13, 2024 at 8:56 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> 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';

my previous email was too verbose.
my point is this: given PK, FK both are case_insensitive. and
PK side values are all being referenced.

case like `update pktable set x = 'a' where x = 'A';`
Do we consider PK side value changed?
it seems not mentioned anywhere.

> 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.

I found out that we don't have tests for
ALTER TABLE ADD FOREIGN KEY
ALTER TABLE ALTER COLUMN SET DATA TYPE.
so I added these. Please ignore previous email attachments.

minor issue on commit message:
"colllations", typo?