CODEWITHSUNDEEP
postgresql
user1613797
user1613797

Reputation: 1267

JSON foreign keys in PostgreSQL

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

Upvotes: 36

Views: 24499

Answers (4)

Luke
Luke

Reputation: 3101

Yes it is possible but you will have to store another value. If you change your schema to:

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

Foreign key that doesn't exist:

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

Returns the error:

ERROR: insert or update on table "data" violates foreign key constraint "data_userid_fkey" DETAIL: Key (userid)=(3) is not present in table "users".

Foreign key that does work:

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

Upvotes: 13

ArtemGr
ArtemGr

Reputation: 12547

Here's a little SPI function have_ids which I use for an integrity constraint on a one-to-many relationship with a jsonb column

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

With a couple of triggers on foo it's almost as good as a foreign key.

Upvotes: 7

Craig Ringer
Craig Ringer

Reputation: 324621

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.

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.

Upvotes: 18

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125344

The foreign key parameter must be a column name:

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

You will have to normalize

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

Upvotes: 3

Related Questions