aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ref/create_table.sgml7
-rw-r--r--src/backend/commands/tablecmds.c84
-rw-r--r--src/backend/utils/adt/ri_triggers.c57
-rw-r--r--src/test/regress/expected/collate.icu.utf8.out100
-rw-r--r--src/test/regress/sql/collate.icu.utf8.sql35
5 files changed, 105 insertions, 178 deletions
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 6262533c57b..1a1adc5ae87 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1206,6 +1206,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
+ For each pair of referencing and referenced column, if they are of a
+ collatable data type, then the collations must either be both
+ deterministic or else both the same. This ensures that both columns
+ have a consistent notion of equality.
+ </para>
+
+ <para>
The user
must have <literal>REFERENCES</literal> permission on the referenced
table (either the whole table, or the specific referenced columns). The
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ccd9645e7d2..c632d1e8245 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -398,10 +398,10 @@ static ObjectAddress ATExecValidateConstraint(List **wqueue,
Relation rel, char *constrName,
bool recurse, bool recursing, LOCKMODE lockmode);
static int transformColumnNameList(Oid relId, List *colList,
- int16 *attnums, Oid *atttypids);
+ int16 *attnums, Oid *atttypids, Oid *attcollids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List **attnamelist,
- int16 *attnums, Oid *atttypids,
+ int16 *attnums, Oid *atttypids, Oid *attcollids,
Oid *opclasses, bool *pk_has_without_overlaps);
static Oid transformFkeyCheckAttrs(Relation pkrel,
int numattrs, int16 *attnums,
@@ -9705,6 +9705,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
int16 fkattnum[INDEX_MAX_KEYS] = {0};
Oid pktypoid[INDEX_MAX_KEYS] = {0};
Oid fktypoid[INDEX_MAX_KEYS] = {0};
+ Oid pkcolloid[INDEX_MAX_KEYS] = {0};
+ Oid fkcolloid[INDEX_MAX_KEYS] = {0};
Oid opclasses[INDEX_MAX_KEYS] = {0};
Oid pfeqoperators[INDEX_MAX_KEYS] = {0};
Oid ppeqoperators[INDEX_MAX_KEYS] = {0};
@@ -9801,11 +9803,11 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
/*
* Look up the referencing attributes to make sure they exist, and record
- * their attnums and type OIDs.
+ * their attnums and type and collation OIDs.
*/
numfks = transformColumnNameList(RelationGetRelid(rel),
fkconstraint->fk_attrs,
- fkattnum, fktypoid);
+ fkattnum, fktypoid, fkcolloid);
with_period = fkconstraint->fk_with_period || fkconstraint->pk_with_period;
if (with_period && !fkconstraint->fk_with_period)
ereport(ERROR,
@@ -9814,7 +9816,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel),
fkconstraint->fk_del_set_cols,
- fkdelsetcols, NULL);
+ fkdelsetcols, NULL, NULL);
validateFkOnDeleteSetColumns(numfks, fkattnum,
numfkdelsetcols, fkdelsetcols,
fkconstraint->fk_del_set_cols);
@@ -9823,13 +9825,14 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* If the attribute list for the referenced table was omitted, lookup the
* definition of the primary key and use it. Otherwise, validate the
* supplied attribute list. In either case, discover the index OID and
- * index opclasses, and the attnums and type OIDs of the attributes.
+ * index opclasses, and the attnums and type and collation OIDs of the
+ * attributes.
*/
if (fkconstraint->pk_attrs == NIL)
{
numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid,
&fkconstraint->pk_attrs,
- pkattnum, pktypoid,
+ pkattnum, pktypoid, pkcolloid,
opclasses, &pk_has_without_overlaps);
/* If the primary key uses WITHOUT OVERLAPS, the fk must use PERIOD */
@@ -9842,7 +9845,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
{
numpks = transformColumnNameList(RelationGetRelid(pkrel),
fkconstraint->pk_attrs,
- pkattnum, pktypoid);
+ pkattnum, pktypoid, pkcolloid);
/* Since we got pk_attrs, one should be a period. */
if (with_period && !fkconstraint->pk_with_period)
@@ -9944,6 +9947,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
Oid pktype = pktypoid[i];
Oid fktype = fktypoid[i];
Oid fktyped;
+ Oid pkcoll = pkcolloid[i];
+ Oid fkcoll = fkcolloid[i];
HeapTuple cla_ht;
Form_pg_opclass cla_tup;
Oid amid;
@@ -10086,6 +10091,41 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
format_type_be(fktype),
format_type_be(pktype))));
+ /*
+ * This shouldn't be possible, but better check to make sure we have a
+ * consistent state for the check below.
+ */
+ if ((OidIsValid(pkcoll) && !OidIsValid(fkcoll)) || (!OidIsValid(pkcoll) && OidIsValid(fkcoll)))
+ elog(ERROR, "key columns are not both collatable");
+
+ if (OidIsValid(pkcoll) && OidIsValid(fkcoll))
+ {
+ bool pkcolldet;
+ bool fkcolldet;
+
+ pkcolldet = get_collation_isdeterministic(pkcoll);
+ fkcolldet = get_collation_isdeterministic(fkcoll);
+
+ /*
+ * SQL requires that both collations are the same. This is
+ * because we need a consistent notion of equality on both
+ * columns. We relax this by allowing different collations if
+ * they are both deterministic. (This is also for backward
+ * compatibility, because PostgreSQL has always allowed this.)
+ */
+ if ((!pkcolldet || !fkcolldet) && pkcoll != fkcoll)
+ ereport(ERROR,
+ (errcode(ERRCODE_COLLATION_MISMATCH),
+ errmsg("foreign key constraint \"%s\" cannot be implemented", fkconstraint->conname),
+ errdetail("Key columns \"%s\" of the referencing table and \"%s\" of the referenced table "
+ "have incompatible collations: \"%s\" and \"%s\". "
+ "If either collation is nondeterministic, then both collations have to be the same.",
+ strVal(list_nth(fkconstraint->fk_attrs, i)),
+ strVal(list_nth(fkconstraint->pk_attrs, i)),
+ get_collation_name(fkcoll),
+ get_collation_name(pkcoll))));
+ }
+
if (old_check_ok)
{
/*
@@ -10106,6 +10146,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
CoercionPathType new_pathtype;
Oid old_castfunc;
Oid new_castfunc;
+ Oid old_fkcoll;
+ Oid new_fkcoll;
Form_pg_attribute attr = TupleDescAttr(tab->oldDesc,
fkattnum[i] - 1);
@@ -10121,6 +10163,9 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
new_pathtype = findFkeyCast(pfeqop_right, new_fktype,
&new_castfunc);
+ old_fkcoll = attr->attcollation;
+ new_fkcoll = fkcoll;
+
/*
* Upon a change to the cast from the FK column to its pfeqop
* operand, revalidate the constraint. For this evaluation, a
@@ -10144,9 +10189,10 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* turn conform to the domain. Consequently, we need not treat
* domains specially here.
*
- * Since we require that all collations share the same notion of
- * equality (which they do, because texteq reduces to bitwise
- * equality), we don't compare collation here.
+ * If the collation changes, revalidation is required, unless both
+ * collations are deterministic, because those share the same
+ * notion of equality (because texteq reduces to bitwise
+ * equality).
*
* We need not directly consider the PK type. It's necessarily
* binary coercible to the opcintype of the unique index column,
@@ -10156,7 +10202,9 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
old_check_ok = (new_pathtype == old_pathtype &&
new_castfunc == old_castfunc &&
(!IsPolymorphicType(pfeqop_right) ||
- new_fktype == old_fktype));
+ new_fktype == old_fktype) &&
+ (new_fkcoll == old_fkcoll ||
+ (get_collation_isdeterministic(old_fkcoll) && get_collation_isdeterministic(new_fkcoll))));
}
pfeqoperators[i] = pfeqop;
@@ -12092,7 +12140,8 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
/*
* transformColumnNameList - transform list of column names
*
- * Lookup each name and return its attnum and, optionally, type OID
+ * Lookup each name and return its attnum and, optionally, type and collation
+ * OIDs
*
* Note: the name of this function suggests that it's general-purpose,
* but actually it's only used to look up names appearing in foreign-key
@@ -12101,7 +12150,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
*/
static int
transformColumnNameList(Oid relId, List *colList,
- int16 *attnums, Oid *atttypids)
+ int16 *attnums, Oid *atttypids, Oid *attcollids)
{
ListCell *l;
int attnum;
@@ -12132,6 +12181,8 @@ transformColumnNameList(Oid relId, List *colList,
attnums[attnum] = attform->attnum;
if (atttypids != NULL)
atttypids[attnum] = attform->atttypid;
+ if (attcollids != NULL)
+ attcollids[attnum] = attform->attcollation;
ReleaseSysCache(atttuple);
attnum++;
}
@@ -12142,7 +12193,7 @@ transformColumnNameList(Oid relId, List *colList,
/*
* transformFkeyGetPrimaryKey -
*
- * Look up the names, attnums, and types of the primary key attributes
+ * Look up the names, attnums, types, and collations of the primary key attributes
* for the pkrel. Also return the index OID and index opclasses of the
* index supporting the primary key. Also return whether the index has
* WITHOUT OVERLAPS.
@@ -12155,7 +12206,7 @@ transformColumnNameList(Oid relId, List *colList,
static int
transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List **attnamelist,
- int16 *attnums, Oid *atttypids,
+ int16 *attnums, Oid *atttypids, Oid *attcollids,
Oid *opclasses, bool *pk_has_without_overlaps)
{
List *indexoidlist;
@@ -12229,6 +12280,7 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
attnums[i] = pkattno;
atttypids[i] = attnumTypeId(pkrel, pkattno);
+ attcollids[i] = attnumCollationId(pkrel, pkattno);
opclasses[i] = indclass->values[i];
*attnamelist = lappend(*attnamelist,
makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno)))));
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 6896e1ae638..91792cb2a47 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -207,7 +207,7 @@ static void ri_BuildQueryKey(RI_QueryKey *key,
int32 constr_queryno);
static bool ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
const RI_ConstraintInfo *riinfo, bool rel_is_pk);
-static bool ri_CompareWithCast(Oid eq_opr, Oid typeid,
+static bool ri_CompareWithCast(Oid eq_opr, Oid typeid, Oid collid,
Datum lhs, Datum rhs);
static void ri_InitHashTables(void);
@@ -776,8 +776,6 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
{
Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
quoteOneName(attname,
RIAttName(fk_rel, riinfo->fk_attnums[i]));
@@ -786,8 +784,6 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
paramname, pk_type,
riinfo->pf_eq_oprs[i],
attname, fk_type);
- if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
- ri_GenerateQualCollation(&querybuf, pk_coll);
querysep = "AND";
queryoids[i] = pk_type;
}
@@ -881,8 +877,6 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
{
Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
quoteOneName(attname,
RIAttName(fk_rel, riinfo->fk_attnums[i]));
@@ -891,8 +885,6 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
paramname, pk_type,
riinfo->pf_eq_oprs[i],
attname, fk_type);
- if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
- ri_GenerateQualCollation(&querybuf, pk_coll);
querysep = "AND";
queryoids[i] = pk_type;
}
@@ -996,8 +988,6 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
{
Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
quoteOneName(attname,
RIAttName(fk_rel, riinfo->fk_attnums[i]));
@@ -1009,8 +999,6 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
paramname, pk_type,
riinfo->pf_eq_oprs[i],
attname, fk_type);
- if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
- ri_GenerateQualCollation(&querybuf, pk_coll);
querysep = ",";
qualsep = "AND";
queryoids[i] = pk_type;
@@ -1232,8 +1220,6 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind)
{
Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
quoteOneName(attname,
RIAttName(fk_rel, riinfo->fk_attnums[i]));
@@ -1243,8 +1229,6 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind)
paramname, pk_type,
riinfo->pf_eq_oprs[i],
attname, fk_type);
- if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
- ri_GenerateQualCollation(&querybuf, pk_coll);
qualsep = "AND";
queryoids[i] = pk_type;
}
@@ -1998,19 +1982,17 @@ ri_GenerateQual(StringInfo buf,
/*
* ri_GenerateQualCollation --- add a COLLATE spec to a WHERE clause
*
- * At present, we intentionally do not use this function for RI queries that
- * compare a variable to a $n parameter. Since parameter symbols always have
- * default collation, the effect will be to use the variable's collation.
- * Now that is only strictly correct when testing the referenced column, since
- * the SQL standard specifies that RI comparisons should use the referenced
- * column's collation. However, so long as all collations have the same
- * notion of equality (which they do, because texteq reduces to bitwise
- * equality), there's no visible semantic impact from using the referencing
- * column's collation when testing it, and this is a good thing to do because
- * it lets us use a normal index on the referencing column. However, we do
- * have to use this function when directly comparing the referencing and
- * referenced columns, if they are of different collations; else the parser
- * will fail to resolve the collation to use.
+ * We only have to use this function when directly comparing the referencing
+ * and referenced columns, if they are of different collations; else the
+ * parser will fail to resolve the collation to use. We don't need to use
+ * this function for RI queries that compare a variable to a $n parameter.
+ * Since parameter symbols always have default collation, the effect will be
+ * to use the variable's collation.
+ *
+ * Note that we require that the collations of the referencing and the
+ * referenced column have the same notion of equality: Either they have to
+ * both be deterministic or else they both have to be the same. (See also
+ * ATAddForeignKeyConstraint().)
*/
static void
ri_GenerateQualCollation(StringInfo buf, Oid collation)
@@ -2952,7 +2934,7 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
* operator. Changes that compare equal will still satisfy the
* constraint after the update.
*/
- if (!ri_CompareWithCast(eq_opr, RIAttType(rel, attnums[i]),
+ if (!ri_CompareWithCast(eq_opr, RIAttType(rel, attnums[i]), RIAttCollation(rel, attnums[i]),
newvalue, oldvalue))
return false;
}
@@ -2968,11 +2950,12 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
* Call the appropriate comparison operator for two values.
* Normally this is equality, but for the PERIOD part of foreign keys
* it is ContainedBy, so the order of lhs vs rhs is significant.
+ * See below for how the collation is applied.
*
* NB: we have already checked that neither value is null.
*/
static bool
-ri_CompareWithCast(Oid eq_opr, Oid typeid,
+ri_CompareWithCast(Oid eq_opr, Oid typeid, Oid collid,
Datum lhs, Datum rhs)
{
RI_CompareHashEntry *entry = ri_HashCompareOp(eq_opr, typeid);
@@ -2998,9 +2981,9 @@ ri_CompareWithCast(Oid eq_opr, Oid typeid,
* on the other side of a foreign-key constraint. Therefore, the
* comparison here would need to be done with the collation of the *other*
* table. For simplicity (e.g., we might not even have the other table
- * open), we'll just use the default collation here, which could lead to
- * some false negatives. All this would break if we ever allow
- * database-wide collations to be nondeterministic.
+ * open), we'll use our own collation. This is fine because we require
+ * that both collations have the same notion of equality (either they are
+ * both deterministic or else they are both the same).
*
* With range/multirangetypes, the collation of the base type is stored as
* part of the rangetype (pg_range.rngcollation), and always used, so
@@ -3008,9 +2991,7 @@ ri_CompareWithCast(Oid eq_opr, Oid typeid,
* But if we support arbitrary types with PERIOD, we should perhaps just
* always force a re-check.
*/
- return DatumGetBool(FunctionCall2Coll(&entry->eq_opr_finfo,
- DEFAULT_COLLATION_OID,
- lhs, rhs));
+ return DatumGetBool(FunctionCall2Coll(&entry->eq_opr_finfo, collid, lhs, rhs));
}
/*
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 6fa32ae3649..de17f7db6ce 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1840,101 +1840,15 @@ SELECT * FROM test4 WHERE b = 'Cote' COLLATE case_insensitive;
1 | cote
(1 row)
--- foreign keys (should use collation of primary key)
--- PK is case-sensitive, FK is case-insensitive
+-- foreign keys (mixing different nondeterministic collations not allowed)
CREATE TABLE test10pk (x text COLLATE case_sensitive PRIMARY KEY);
-INSERT INTO test10pk VALUES ('abc'), ('def'), ('ghi');
-CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CASCADE ON DELETE CASCADE);
-INSERT INTO test10fk VALUES ('abc'); -- ok
-INSERT INTO test10fk VALUES ('ABC'); -- error
-ERROR: insert or update on table "test10fk" violates foreign key constraint "test10fk_x_fkey"
-DETAIL: Key (x)=(ABC) is not present in table "test10pk".
-INSERT INTO test10fk VALUES ('xyz'); -- error
-ERROR: insert or update on table "test10fk" violates foreign key constraint "test10fk_x_fkey"
-DETAIL: Key (x)=(xyz) is not present in table "test10pk".
-SELECT * FROM test10pk;
- x
------
- abc
- def
- ghi
-(3 rows)
-
-SELECT * FROM test10fk;
- x
------
- abc
-(1 row)
-
--- restrict update even though the values are "equal" in the FK table
-UPDATE test10fk SET x = 'ABC' WHERE x = 'abc'; -- error
-ERROR: insert or update on table "test10fk" violates foreign key constraint "test10fk_x_fkey"
-DETAIL: Key (x)=(ABC) is not present in table "test10pk".
-SELECT * FROM test10fk;
- x
------
- abc
-(1 row)
-
-DELETE FROM test10pk WHERE x = 'abc';
-SELECT * FROM test10pk;
- x
------
- def
- ghi
-(2 rows)
-
-SELECT * FROM test10fk;
- x
----
-(0 rows)
-
--- PK is case-insensitive, FK is case-sensitive
+CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CASCADE ON DELETE CASCADE); -- error
+ERROR: foreign key constraint "test10fk_x_fkey" cannot be implemented
+DETAIL: Key columns "x" of the referencing table and "x" of the referenced table have incompatible collations: "case_insensitive" and "case_sensitive". If either collation is nondeterministic, then both collations have to be the same.
CREATE TABLE test11pk (x text COLLATE case_insensitive PRIMARY KEY);
-INSERT INTO test11pk VALUES ('abc'), ('def'), ('ghi');
-CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCADE ON DELETE CASCADE);
-INSERT INTO test11fk VALUES ('abc'); -- ok
-INSERT INTO test11fk VALUES ('ABC'); -- ok
-INSERT INTO test11fk VALUES ('xyz'); -- error
-ERROR: insert or update on table "test11fk" violates foreign key constraint "test11fk_x_fkey"
-DETAIL: Key (x)=(xyz) is not present in table "test11pk".
-SELECT * FROM test11pk;
- x
------
- abc
- def
- ghi
-(3 rows)
-
-SELECT * FROM test11fk;
- x
------
- abc
- ABC
-(2 rows)
-
--- cascade update even though the values are "equal" in the PK table
-UPDATE test11pk SET x = 'ABC' WHERE x = 'abc';
-SELECT * FROM test11fk;
- x
------
- ABC
- ABC
-(2 rows)
-
-DELETE FROM test11pk WHERE x = 'abc';
-SELECT * FROM test11pk;
- x
------
- def
- ghi
-(2 rows)
-
-SELECT * FROM test11fk;
- x
----
-(0 rows)
-
+CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCADE ON DELETE CASCADE); -- error
+ERROR: foreign key constraint "test11fk_x_fkey" cannot be implemented
+DETAIL: Key columns "x" of the referencing table and "x" of the referenced table have incompatible collations: "case_sensitive" and "case_insensitive". If either collation is nondeterministic, then both collations have to be the same.
-- partitioning
CREATE TABLE test20 (a int, b text COLLATE case_insensitive) PARTITION BY LIST (b);
CREATE TABLE test20_1 PARTITION OF test20 FOR VALUES IN ('abc');
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 49fa9758b40..0c9491c260e 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -687,39 +687,12 @@ SELECT * FROM test4 WHERE b = 'cote' COLLATE ignore_accents;
SELECT * FROM test4 WHERE b = 'Cote' COLLATE ignore_accents; -- still case-sensitive
SELECT * FROM test4 WHERE b = 'Cote' COLLATE case_insensitive;
--- foreign keys (should use collation of primary key)
-
--- PK is case-sensitive, FK is case-insensitive
+-- foreign keys (mixing different nondeterministic collations not allowed)
CREATE TABLE test10pk (x text COLLATE case_sensitive PRIMARY KEY);
-INSERT INTO test10pk VALUES ('abc'), ('def'), ('ghi');
-CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CASCADE ON DELETE CASCADE);
-INSERT INTO test10fk VALUES ('abc'); -- ok
-INSERT INTO test10fk VALUES ('ABC'); -- error
-INSERT INTO test10fk VALUES ('xyz'); -- error
-SELECT * FROM test10pk;
-SELECT * FROM test10fk;
--- restrict update even though the values are "equal" in the FK table
-UPDATE test10fk SET x = 'ABC' WHERE x = 'abc'; -- error
-SELECT * FROM test10fk;
-DELETE FROM test10pk WHERE x = 'abc';
-SELECT * FROM test10pk;
-SELECT * FROM test10fk;
-
--- PK is case-insensitive, FK is case-sensitive
+CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CASCADE ON DELETE CASCADE); -- error
+
CREATE TABLE test11pk (x text COLLATE case_insensitive PRIMARY KEY);
-INSERT INTO test11pk VALUES ('abc'), ('def'), ('ghi');
-CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCADE ON DELETE CASCADE);
-INSERT INTO test11fk VALUES ('abc'); -- ok
-INSERT INTO test11fk VALUES ('ABC'); -- ok
-INSERT INTO test11fk VALUES ('xyz'); -- error
-SELECT * FROM test11pk;
-SELECT * FROM test11fk;
--- cascade update even though the values are "equal" in the PK table
-UPDATE test11pk SET x = 'ABC' WHERE x = 'abc';
-SELECT * FROM test11fk;
-DELETE FROM test11pk WHERE x = 'abc';
-SELECT * FROM test11pk;
-SELECT * FROM test11fk;
+CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCADE ON DELETE CASCADE); -- error
-- partitioning
CREATE TABLE test20 (a int, b text COLLATE case_insensitive) PARTITION BY LIST (b);
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-06 09:33:45 +0000