為什麼 PostgreSQL 允許外鍵中的某些類型不匹配？
在教授數據庫基礎課程時，一名學生詢問了外鍵，其數據類型與他們所引用的事物的數據類型（例如主鍵）不匹配。
例如，可以儲存在
INTEGER列中的所有數字都可以表示為TEXT，因此只要應用適當的類型轉換/轉換，TEXT列的數據就可以用於引用列中的數據。INTEGER我們在教學中使用 PostgreSQL（因為它的優秀文件等等），所以我們去看看。瞧，關於外鍵的“簡化”章節告訴我們：
當然，約束列的數量和類型需要與引用列的數量和類型相匹配。
不過，關於CREATE TABLE的“功能完整”部分的進一步研究並未明確提及數據類型。這部分只談價值觀。
我們嘗試了各種數據類型的組合，有些組合比其他的更有說服力（比如上面的
INTEGER-TEXT變體）。DBMS 不服氣並回答42804：不兼容的類型。到現在為止還挺好。想像一下，當我們發現 PostgreSQL 的各種 Integer-types確實有效時，我們感到非常驚訝。
他們甚至正確地考慮了符號，這意味著它們不僅僅是匹配位。
當然，這應該有一個方向：讓
INTEGER列引用的BIGINT列總是有效的，因為適合引用列的所有內容也適合引用列。令人驚訝的是，PostgreSQL 允許另一個方向（在這個例子中使用
INTEGERand ）：SMALLINTCREATE TABLE this_should_not_work ( this_should_not_work_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, data TEXT ); CREATE TABLE this_should_not_work_detail ( detail_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, detail_data TEXT, -- This Foreign Key references a column of a different type, which should not be possible fk_this_should_not_work_id SMALLINT REFERENCES this_should_not_work (this_should_not_work_id) );這是上述問題的可執行版本：db-fiddle
db-fiddle 還具有
INSERT在第二次插入時觸發失敗的序列選項和 -Statements。請注意，上面連結的文件和 db-fiddle 適用於 PostgreSQL 13，但問題（？）也可以在 PostgreSQL 14 上重現。
我知道外鍵類型不匹配是一個數據庫設計問題。問題是，為什麼 PostgreSQL 會指出明顯的情況（
INTEGER-TEXT），而不是更微妙的情況（INTEGER-SMALLINT）？PS：當它與 結合使用時，可能會導致很多破壞
ON UPDATE CASCADE，因為由於數據不適合引用表而導致對引用表的更新失敗 - 這會產生一個相當“創造性”的錯誤消息。
使用來源，盧克！
在
ATAddForeignKeyConstraintin 中src/backend/commands/tablecmds.c，我們找到了需求的真相：/* * There had better be a primary equality operator for the index. * We'll use it for PK = PK comparisons. */ ppeqop = get_opfamily_member(opfamily, opcintype, opcintype, eqstrategy); if (!OidIsValid(ppeqop)) elog(ERROR, "missing operator %d(%u,%u) in opfamily %u", eqstrategy, opcintype, opcintype, opfamily);因此，目標類型的唯一索引必須支持相等比較。
/* * Are there equality operators that take exactly the FK type? Assume * we should look through any domain here. */ fktyped = getBaseType(fktype); pfeqop = get_opfamily_member(opfamily, opcintype, fktyped, eqstrategy); if (OidIsValid(pfeqop)) { pfeqop_right = fktyped; ffeqop = get_opfamily_member(opfamily, fktyped, fktyped, eqstrategy); } else { /* keep compiler quiet */ pfeqop_right = InvalidOid; ffeqop = InvalidOid; }如果引用列的數據類型和目標索引支持的被引用列之間存在相等運算符，我們很好。
if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) { /* * Otherwise, look for an implicit cast from the FK type to the * opcintype, and if found, use the primary equality operator. * This is a bit tricky because opcintype might be a polymorphic * type such as ANYARRAY or ANYENUM; so what we have to test is * whether the two actual column types can be concurrently cast to * that type. (Otherwise, we'd fail to reject combinations such * as int[] and point[].) */ Oid input_typeids[2]; Oid target_typeids[2]; input_typeids[0] = pktype; input_typeids[1] = fktype; target_typeids[0] = opcintype; target_typeids[1] = opcintype; if (can_coerce_type(2, input_typeids, target_typeids, COERCION_IMPLICIT)) { pfeqop = ffeqop = ppeqop; pfeqop_right = opcintype; } }否則，必須有從引用列的類型到被引用列的隱式轉換。
if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("foreign key constraint \"%s\" cannot be implemented", fkconstraint->conname), errdetail("Key columns \"%s\" and \"%s\" " "are of incompatible types: %s and %s.", strVal(list_nth(fkconstraint->fk_attrs, i)), strVal(list_nth(fkconstraint->pk_attrs, i)), format_type_be(fktype), format_type_be(pktype))));如果兩者都不是真正的錯誤。
所以你可以有外鍵 from
integertosmallint因為這些類型之間存在一個屬於索引運算符系列的相等運算符：\do = List of operators Schema │ Name │ Left arg type │ Right arg type │ Result type │ Description ════════════╪══════╪═════════════════════════════╪═════════════════════════════╪═════════════╪═══════════════ ... pg_catalog │ = │ integer │ smallint │ boolean │ equal ... (63 rows)
text但是， and之間沒有隱式轉換integer，因此這些類型之間不能有外鍵引用。\dC List of casts Source type │ Target type │ Function │ Implicit? ═════════════════════════════╪═════════════════════════════╪════════════════════╪═══════════════ ... integer │ bigint │ int8 │ yes integer │ bit │ bit │ no integer │ boolean │ bool │ no integer │ "char" │ char │ no integer │ double precision │ float8 │ yes integer │ money │ money │ in assignment integer │ numeric │ numeric │ yes integer │ oid │ (binary coercible) │ yes integer │ real │ float4 │ yes integer │ regclass │ (binary coercible) │ yes integer │ regcollation │ (binary coercible) │ yes integer │ regconfig │ (binary coercible) │ yes integer │ regdictionary │ (binary coercible) │ yes integer │ regnamespace │ (binary coercible) │ yes integer │ regoper │ (binary coercible) │ yes integer │ regoperator │ (binary coercible) │ yes integer │ regproc │ (binary coercible) │ yes integer │ regprocedure │ (binary coercible) │ yes integer │ regrole │ (binary coercible) │ yes integer │ regtype │ (binary coercible) │ yes integer │ smallint │ int2 │ in assignment ...