PostgreSQL中的JSON外键 [英] JSON foreign keys in PostgreSQL

问题描述

是否可以在PostgreSQL中将外键分配给json属性？这是我想要实现的示例，但是它不起作用：

Is it possible to assign a foreign key to a json property in PostgreSQL? Here is an example what I would like to achieve, but it doesn't work:

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
);

推荐答案

不可能，也许永远不可能，将外键分配给json属性。对PostgreSQL的外键执行来说，这将是一个重大且非常复杂的变化。我认为这并非不可能，但是会遇到与外键到数组补丁所遇到的问题类似的问题。

It is not possible, and may not ever be possible, to assign a foreign key to a json property. It'd be a major and quite complicated change to PostgreSQL's foreign key enforcement. I don't think it's impossible to do, but would face similar issues to those experienced by the foreign-keys-to-arrays patch.

对于9.4，它将是可能使整个json对象成为外键，因为 jsonb 支持相等性测试。在9.3中，您甚至无法做到这一点。

With 9.4 it'll be possible to make a whole json object a foreign key as jsonb supports equality tests. In 9.3 you can't even do that.

这篇关于PostgreSQL中的JSON外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！