主页 / dba / 问题 / 339836
Accepted
ceving
Asked: 2024-05-27 23:22:12 +0800 CST

为什么我的部分唯一索引不足以用作外键？

  • 772

我有以下代码，它会引发错误：

错误：没有与引用表“person”的给定键匹配的唯一约束

DROP TABLE IF EXISTS person;
CREATE TABLE person (
  sn        serial     PRIMARY KEY,
  id        int        NOT NULL,
  modified  timestamp,
  name      text
);
CREATE UNIQUE INDEX person_id_key ON person (id) WHERE modified IS NULL;
DROP TABLE IF EXISTS account;
CREATE TABLE account (
  id        int        NOT NULL REFERENCES person(id),
  name      text
);

有一个唯一索引id：

                                       Table "public.person"
  Column  |            Type             | Collation | Nullable |              Default               
----------+-----------------------------+-----------+----------+------------------------------------
 sn       | integer                     |           | not null | nextval('person_sn_seq'::regclass)
 id       | integer                     |           | not null | 
 modified | timestamp without time zone |           |          | 
 name     | text                        |           |          | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (sn)
    "person_id_key" UNIQUE, btree (id) WHERE modified IS NULL

为什么外键不够？

postgresql

1 个回答

  1. Best Answer

    这有点像怪物 - < TL:DR> 转到附录 </TL:DR>

    你的问题：

    为什么外键不够？

    因为，您的索引person_id_key UNIQUE, btree (id) WHERE modified IS NULL仅涵盖一小部分记录的唯一性person.id。为了使字段成为FOREIGN KEY，它必须覆盖所有记录person.id！

    您必须做一些不同的事情，并在表创建时CREATE创建UNIQUE索引，然后创建部分索引（显然，并且使用不同的名称）。person.idCREATE

    所有的代码都在这里。

    你所做的是这样的：

    CREATE TABLE person 
(
  sn        SERIAL     PRIMARY KEY,
  id        INTEGER    NOT NULL UNIQUE,  -- CREATE the UNIQUE index at table creation!
  modified  TIMESTAMP,
  name      TEXT
);

    然后是CREATE你的账户表：

    CREATE TABLE account 
(
  id        INTEGER        NOT NULL REFERENCES person(id),
  name      TEXT
);

    当成功后，现在我们CREATE的部分索引：

    CREATE UNIQUE INDEX person_idx_key ON person (id) WHERE modified IS NULL;

    然后我们运行以下查询来检查：

    SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  c2.relname as "Table"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','I','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

    结果：

    Schema  Name           Type   Owner     Table
public  person_id_key  index  postgres  person
public  person_idx_key index  postgres  person
public  person_pkey    index  postgres  person

    瞧！您可以通过运行\set ECHO_HIDDEN oninpsql然后运行 ​​来获取上面的 SQL \di。设置ECHO_HIDDEN变量意味着当您发出psql元命令（即不是 SQL）时，会给出该命令生成的 SQL - 这是学习 PostgreSQL 的好方法！

    有几点：

    • 我可以建议您使用TIMESTAMPTZ（带时区）而不只是TIMESTAMP- 这只是更好的做法 - 即使您从未计划搬到不同的国家/地区，仍然需要考虑 DST（夏令时）问题。而且，即使您居住的地区现在没有夏令时，将来也可能会实行

    • 另外，我可以建议您为索引指定有意义的名称，例如将UNIQUE索引添加为_uq或类似的后缀。

    • 这也有效！

    附录 - 回应评论中OP的问题：

    你能给你答案第一段的参考吗？这句话总是正确的吗？

    对于那些已经忘记的人来说，第一段是：

    因为，您的索引 person_id_key UNIQUE, btree (id) WHERE modded IS NULL 仅涵盖一小部分 person.id 记录的唯一性。为了使字段成为外键，它必须覆盖所有 person.id 记录！

    在你的问题促使我进行调查之前，我真的从未深入思考过这个问题 - 我真的很高兴我这么做了。我的发现让我感到惊讶，但我也很感激我的分析结果让我有了一种顿悟。

    我的第一条线索来自@ErwinBrandstetter，他写道：

    如果您定义了唯一索引，则可以安全地假设值是唯一的。这就是独特约束的实现方式（目前，也可能在所有未来版本中）。

    UNIQUE CONSTRAINT现在，请注意 a和 a之间的区别UNIQUE INDEX- 我花了更多的搜索、修改和阅读才能最终掌握它。

    他接着说：

    定义 UNIQUE 约束实际上与创建唯一索引而不指定索引类型相同（几乎见下文）。

    因此，再次进行区分 - 不确定他所说的“没有......索引类型”是什么意思。

    从这里（再次@EB），我们有：

    外键必须引用作为主键或形成唯一约束的列。这意味着引用的列总是有一个索引（主键或唯一约束的基础索引）；因此检查引用行是否有匹配将是有效的。

    因此， aCONSTRAINT有一个底层 INDEX，但它本身不仅仅是一个索引 - 它不仅仅是一个索引！

    进一步的线索可以在这里找到：

    使用 UNIQUE INDEX 与 UNIQUE CONSTRAINT 相比的另一个优点是，您可以轻松地同时删除/创建索引，而使用约束则不能。

    EB（第一个链接）说：

    独特的约束可以被推迟。对于唯一索引来说这是不可能的。

    因此，这是UNIQUE约束和索引之间的两个记录差异。

    当我偶然发现这颗宝石时，一切终于对我来说一切就位了——它证明了两者之间存在很大的区别：

    SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';

    因此，根据您的情况，我修改了原来的小提琴并包含了我学到的内容。

    我用小提琴掉进了兔子洞，所以你可以忽略除了最后一个片段（上面的 SQL）之外的所有内容 - 请注意，现在有 2 条记录，而不是3 条记录！

    SQL 结果（仅显示前两个字段）：

    oid     conname              
16392   person_sn_p_key     
16394   person_id_uq_constr

    其中一个代表表PRIMARY KEY的person，另一个代表（整体）UNIQUE CONSTRAINT- “缺少”的是部分UNIQUE INDEX，那是因为它不是约束！

    我对自己合理化的方式是， aCONSTRAINT能够阻止东西进入表中 - 一旦它通过了所有约束，INDEXes 的工作就是提供指针，以便如果优化器可以更快地访问它认为应该使用它 - 即仅仅是实现细节。

    最后的小提琴- 我将其更改为ALTER TABLE ADD CONSTRAINT...，CREATE INDEX...现在 SQL 中只有 1 条记录CONSTRAINT- 但帐户FK不会失败 - 因为它覆盖了整个表。

    • CONSTRAINTs = 保护

    • INDEXs = 组织（除非它们覆盖整个表格）

    • CONSTRAINTs - SQL 标准强制要求

    • INDEXes -未强制要求

    所以，回答问题

    • 参考？是的 - 请参阅链接

    • 两种类型的UNIQUE索引？其实，更准确的说法是，分为三种

      • UNIQUE CONSTRAINTs - 可用于FKs
      • UNIQUE INDEXes（未限定）- 可用于FKs
      • UNIQUE INDEXFKes（部分）- 不能在s中使用

    瞧，+1 真的让我思考！

    其他有用的链接：

    • 4

相关问题