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

Why don't you take id from the json and use as FK in your table.. — cracker, Jun 30 '14 at 12:19
@cracker: this is just an example; in my project I have an array of items in json field - without such constraint it will require to create a new table... — user1613797, Jun 30 '14 at 12:28
if you don't have the table then how can you give the foreign key? — cracker, Jun 30 '14 at 12:36
You could create a trigger that extracts the id value from the JSON when the column is changed and then put it into a real column that can be used to define a foreign key. — , Jun 30 '14 at 13:09

score 17 · Accepted Answer · answered Jul 01 '14 at 01:43

17

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.

answered Jul 01 '14 at 01:43

Craig Ringer

307,061
76
688
778

1

Today (2015's), with stable pg9.4+ version, and its community, there are some "good practice" or usual pg-community convention to express foreign key (with formal or informal pg-assign)? See [this old linking_in_json](https://www.mnot.net/blog/2011/11/25/linking_in_json) as ref to "JSON link conventions" ... Example, here @ArtemGr show alternative, and to express ID in a row, I like [JSON Reference](https://tools.ietf.org/html/draft-pbryan-zyp-json-ref-03), but it is used in pg-community?) – Peter Krauss May 17 '15 at 12:19
@PeterKrauss No, not really. The situation hasn't changed significantly, much like foreign keys to array fields. There are some difficult performance issues to deal with even if someone wanted to implement it, and so far nobody does. – Craig Ringer May 18 '15 at 00:28

score 7 · Answer 2 · edited Apr 09 '18 at 05:41

7

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.

edited Apr 09 '18 at 05:41

answered Aug 06 '14 at 08:59

ArtemGr

11,684
3
52
85

Do you have a benchmark showing the performance (ideally comparing with its analog in SQL) of your `have_ids()` function? Even with "bad performance", if it is generic and stable, will be a good proposal to PostgreSQL community. – Peter Krauss May 18 '15 at 15:12
2

No, I haven't benchmarked it, but the performance characteristics seems very transparent to me. The function loops over the the JSONB array and performs a `SELECT COUNT(*) FROM` query for every ID. It's as fast as the sum of these queries. The performance cost of the function itself should be neglible. I don't have an SQL version, neither I know how to write one. I think PosgreSQL will eventually have first-class support for array-based foreign keys (https://wiki.postgresql.org/wiki/Todo#Referential_Integrity), so this workaround shouldn't be needed in the long term. – ArtemGr May 18 '15 at 16:00
@ArtemGr i know it's old, but you could probably increase performance of that loop with `COUNT(1)` instead of `COUNT(*)` – Sampson Crowley Jul 16 '20 at 20:00
1

@SampsonCrowley I haven't tested it in a while but IIRC the `COUNT(*)` is optimized away by PostgreSQL (e.g. the star is not expanded into any fields) and I tend to keep `COUNT(*)` for readability (POLA). – ArtemGr Jul 17 '20 at 00:44
@ArtemGr ah, well I'm out of date then; I always `COUNT(1)` because it has the same readability (to me) and I've always heard it's more performant than counting column(s) – Sampson Crowley Jul 17 '20 at 00:58
@ArtemGr and it looks like you're right, they're actually both optimized away because of superstitious people such as myself: https://stackoverflow.com/a/1221649/9196467 – Sampson Crowley Jul 17 '20 at 01:00
Would not `COUNT(*) LIMIT 1` be the most optimized? So that you do not have to count exact number of rows matching the query, you care only if it is non-zero? – Mitar Apr 12 '21 at 17:01
@Mitar `"SELECT 1 FROM " << table << " WHERE id = " << id << " LIMIT 1"` then, but you ought to benchmark the query in your specific context first. – ArtemGr Apr 12 '21 at 18:11

score 3 · Answer 3 · answered Jun 30 '14 at 13:01

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

score 3 · Answer 4 · answered Sep 19 '22 at 07:29

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

JSON foreign keys in PostgreSQL

4 Answers4

Linked