JSON foreign keys in PostgreSQL

Question

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

Answer 1

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.

Answer 2

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.