Re: altering a column's collation leaves an invalid foreign key
От | Peter Eisentraut |
---|---|
Тема | Re: altering a column's collation leaves an invalid foreign key |
Дата | |
Msg-id | f43e31bd-3489-4545-a4d0-17356ee6af5d@eisentraut.org обсуждение исходный текст |
Ответ на | altering a column's collation leaves an invalid foreign key (Paul Jungwirth <pj@illuminatedcomputing.com>) |
Список | pgsql-hackers |
On 25.10.24 08:23, jian he wrote: > ri_KeysEqual definitely deserves some comments. > for rel_is_pk, the equality is collation agnostic; > for rel_is_pk is false, the equality is collation aware. > > for example: > 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'; > ERROR: update or delete on table "pktable" violates foreign key > constraint "fktable_x_fkey" on table "fktable" > DETAIL: Key (x)=(A) is still referenced from table "fktable". > this should not happen? 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.
В списке pgsql-hackers по дате отправления: