Внешние ключи JSON в PostgreSQL

Можно ли назначить внешний ключ для свойства json в PostgreSQL? Вот пример, который я хотел бы достичь, но он не работает:

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

Ответ 1

Невозможно и, возможно, никогда не будет возможно назначить внешний ключ для свойства json. Это было бы серьезным и довольно сложным изменением в применении внешнего ключа PostgreSQL. Я не думаю, что это невозможно сделать, но столкнулись бы с подобными проблемами с теми, которые испытывались с помощью патча external-keys-to-arrays.

С 9.4 можно будет сделать весь json-объект внешним ключом, поскольку jsonb поддерживает тесты на равенство. В 9.3 вы даже не можете этого сделать.

Ответ 2

Параметр внешнего ключа должен быть именем столбца:

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

Ответ 3

Здесь небольшая функция SPI have_ids, которую я использую для ограничения целостности для отношения "один ко многим" с столбцом jsonb: https://gist.github.com/ArtemGr/67196668a1ea22cfda66

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 он почти так же хорош, как и внешний ключ.