On Wed, Nov 13, 2024 at 8:56 PM jian he <jian.universal...@gmail.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?
v8-0001-add-more-tests-for-pk-fk-tie-with-nondetermin.no-cfbot1
Description: Binary data
