PostgreSQL中的JSON外键

Question

在PostgreSQL中，是否可以将外键分配给JSON属性？以下是我想要实现的示例，但它无法正常工作：

CREATE TABLE Users (Id int NOT NULL PRIMARY KEY);

CREATE TABLE Data (
    Id int NOT NULL PRIMARY KEY,
    JsonData json NOT NULL, -- [{Id: 1, somedata: null},{Id: 2, somedata: null}, ...]
    CONSTRAINT FK_Users_Data FOREIGN KEY (JsonData->Id) REFERENCES Users(Id) -- this constraint will fail
);

- user1613797

---

不，那是不可能的。 - user330315

为什么不从JSON中获取ID并将其用作表中的外键？ - cracker

@cracker：这只是一个例子；在我的项目中，我有一个JSON字段中的项目数组 - 如果没有这样的约束条件，它将需要创建一个新表... - user1613797

如果您没有该表格，那么您怎么能提供外键呢？ - cracker

你可以创建一个触发器，当列被更改时从JSON中提取id值，并将其放入一个真实的列中，以便用于定义外键。 - user330315

Answer 1

无法将外键分配给json属性，也许永远不可能做到。这将是对PostgreSQL外键实施的一个重大而相当复杂的更改。我不认为这是不可能的，但会遇到与外键到数组补丁类似的问题。

使用9.4，可以将整个json对象作为外键，因为jsonb支持等式测试。在9.3中，甚至无法做到这一点。

- Craig Ringer

---

今天（2015年），随着稳定的pg9.4+版本及其社区的发展，是否有一些“最佳实践”或通常的pg-community约定来表示外键（使用正式或非正式的pg-assign）？请参考这个旧的linking_in_json作为“JSON链接约定”的参考...例如，@ArtemGr在这里展示了替代方案，并且要在行中表示ID，我喜欢JSON Reference，但它是否被用于pg-community？ - Peter Krauss

@PeterKrauss 不，实际上并没有。情况并没有发生显著变化，就像外键到数组字段一样。即使有人想要实现它，也有一些困难的性能问题需要处理，但目前还没有人这样做。 - Craig Ringer

Answer 2

这里有一个小的SPI函数have_ids，我用它来对jsonb列的一对多关系进行完整性约束。

CREATE TABLE foo (
  id INTEGER NOT NULL
)

CREATE TABLE bar (
  foo_ids pg_catalog.jsonb DEFAULT '[]'::jsonb NOT NULL,
  CONSTRAINT bar_fooids_chk CHECK (have_ids ('foo', foo_ids))
)

通过对foo上的几个触发器进行设置，可以实现与外键相近的效果。

- ArtemGr

---

你有展示have_ids()函数性能的基准测试吗？最好能与SQL中的类似函数进行比较。即使性能不佳，只要它是通用且稳定的，也将成为PostgreSQL社区的一个好建议。 - Peter Krauss

不，我没有对其进行基准测试，但性能特征对我来说似乎非常透明。该函数循环遍历JSONB数组，并为每个ID执行SELECT COUNT(*) FROM查询。它的速度与这些查询的总和一样快。函数本身的性能成本应该是可以忽略不计的。我没有SQL版本，也不知道如何编写SQL版本。我认为PosgreSQL最终将支持基于数组的外键（https://wiki.postgresql.org/wiki/Todo#Referential_Integrity），因此长期而言不需要这种解决方法。 - ArtemGr

@SampsonCrowley 我已经有一段时间没有测试它了，但我记得 PostgreSQL 会优化 COUNT(*) (例如，星号不会扩展到任何字段)，而且我倾向于保持 COUNT(*) 的可读性（POLA）。 - ArtemGr

“COUNT(*) LIMIT 1” 不是最优化的吗？这样你就不必计算与查询匹配的行数，只需关心它是否为非零即可。 - Mitar

@Mitar "SELECT 1 FROM " << table << " WHERE id = " << id << " LIMIT 1"，但你应该先在特定的上下文中对查询进行基准测试。 - ArtemGr

Answer 3

是的，这是可能的，但您将需要存储另一个值。如果您将模式更改为：

CREATE TABLE Users (Id int NOT NULL PRIMARY KEY);

CREATE TABLE Data (
    Id int NOT NULL PRIMARY KEY,
    JsonData json NOT NULL,
    UserId int generated always as ((JsonData->>'Id')::int) stored references Users(Id)
);

INSERT INTO Users VALUES (1);

不存在的外键：

INSERT INTO Data VALUES (1, '{"Id": 3}');

返回错误：

错误：在表“data”上插入或更新违反了外键约束“data_userid_fkey”详细信息：(userid)=(3)的关键字不存在于表“users”中。

有效的外键：

INSERT INTO Data VALUES (1, '{"Id": 1}');

- Luke

Answer 4

外键参数必须是列名：

http://www.postgresql.org/docs/current/static/sql-createtable.html

你需要进行规范化处理

create table user_data (
    id int not null primary key,
    user_id int not null,
    somedata text,
    constraint fk_users_data foreign key (user_id) references Users(Id)
);

- Clodoaldo Neto