
/*
$CONN6 -a < /home/jian/Desktop/patch_tmp/pk_fk_inderministic_collation.sql > test.out 2>&1
especially pay attention to `table fktable;` output
*/
CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false);

--on update cascade on delete cascade 
drop table if exists pktable, fktable;
CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
CREATE TABLE fktable (x text collate case_insensitive REFERENCES pktable REFERENCES pktable on update cascade on delete cascade );
INSERT INTO pktable VALUES ('A'), ('Å');
INSERT INTO fktable VALUES ('a');
update pktable set x = 'a' where x = 'A';
table fktable;

--on update set default(x) on delete set default(x)
drop table if exists pktable, fktable;
CREATE TABLE pktable (x text collate case_insensitive PRIMARY KEY);
CREATE TABLE fktable (x text collate case_insensitive default 'A' REFERENCES pktable on update set default on delete set default);
INSERT INTO pktable VALUES ('A'), ('Å');
INSERT INTO fktable VALUES ('a');
update pktable set x = 'a' where x = 'A';
table fktable;

-- on update restirct on delete restirct
drop table if exists pktable, fktable;
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';
table fktable;


-- on update set null on delete set null
drop table if exists pktable, fktable;
CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
CREATE TABLE fktable (x text collate case_insensitive REFERENCES pktable on update set null on delete set null);
INSERT INTO pktable VALUES ('A'), ('Å');
INSERT INTO fktable VALUES ('a');
update pktable set x = 'a' where x = 'A';
table fktable;