主页 / dba / 问题 / 64270
Accepted
davetapley
Asked: 2014-05-01 22:09:13 +0800 CST

Postgresql中JSON数据中的主键

  • 772

我有一个表t，有一个名为 的列json，类型为JSON。在 JSON 中有一个自然键：

> SELECT json->'id' AS id FROM t LIMIT 1;
             id              
-----------------------------
 " 63631ff3809de7a17398602f"

我可以创建一个UNIQUE INDEXon id，因此：

> CREATE UNIQUE INDEX t_id ON t((json->>'id'));                                                                                                                       

CREATE INDEX

我想将其添加为table_constraint_using_index，但两者都失败PRIMARY KEY：

> ALTER TABLE t ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX t_id;                                                                                   
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_pkey
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

和UNIQUE：

> ALTER TABLE t ADD CONSTRAINT t_unique_id UNIQUE USING INDEX t_id;
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_unique_id...
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

我应该能够添加这样的约束吗？

postgresql-9.3 constraint

2 个回答

  1. Best Answer

    不，您不应该添加这样的约束。

    PRIMARY KEY必须是简单或复合索引。它可能不是部分索引或表达式索引。

    索引充当约束，在功能上它们大致相同，但它不能作为PRIMARY KEY表元数据中的 出现，也不能用作外键约束的目标。UNIQUE约束也是如此。

    这里的问题是PRIMARY KEY和UNIQUE约束的 SQL 标准定义不允许表达式或行匹配谓词。因此，如果 PostgreSQL 将表达式索引或部分索引列为约束，那么它就违反了标准，并向应用程序谎报它在做什么。了解 PostgreSQL 特性的应用可以从 Pg 自己的目录中查找索引，其中也有 info information_schema，但不能作为列出的约束进入。

    • 6

  2. 正如克雷格的回答中所解释的那样，既PRIMARY KEY不能也UNIQUE不能使用。

    但是，这是解决此问题的两部分解决方案：

    • 要强制执行not null，请使用检查约束，因此：

      ALTER TABLE t ADD CONSTRAINT id_not_null CHECK ((json->>'id') IS NOT NULL);

    • 要强制执行唯一性，请创建唯一索引*，因此：

      CREATE UNIQUE INDEX t_id ON t ((json->>'id'))

    在此之后，一个\d ton inpsql会给你：

    Indexes:
    "t_id" UNIQUE, btree ((json ->> 'id'::text))
Check constraints:
    "id_not_null" CHECK ((json ->> 'id'::text) IS NOT NULL)

    您可以看到非空和唯一性都被强制执行：

    test=# INSERT INTO t VALUES ('{"no id" : "field here"}');
ERROR:  new row for relation "t" violates check constraint "id_not_null"
DETAIL:  Failing row contains ({"no id" : "field here"}).

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
INSERT 0 1

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
ERROR:  duplicate key value violates unique constraint "t_id"
DETAIL:  Key ((json ->> 'id'::text))=(abc) already exists

    * 请注意，仅t_id索引的存在就足以强制执行唯一性，即使它没有明确地作为约束给出。

    • 6

相关问题