SQL: table constraint to reject rows based on table contents

Question

In Postgres, I am trying to find a way to enforce a special kind of constraint across two columns.

I have a table with this schema (the relevant bits):

CREATE TABLE resources (
  id         SERIAL      PRIMARY KEY,
  org_id     INT         NOT NULL,
  source_id  INT         NOT NULL,
  data       JSON        NOT NULL  -- to keep it simple, it could be anything
);

Some example data:

id    | org_id   | source_id    | data
---------------------------------------
1     | 11       | 1001         | {...}
2     | 22       | 2002         | {...}
3     | 33       | 3003         | {...}
4     | 11       | 1001         | {...}
5     | 11       | 2002         | {...}  -- This should be invalid.

Assuming the data value of all rows is different, both rows 1 and 4 are valid. They're just different resources linked to the same org_id and source_id.

However, I never want a source_id to be used with different org_ids. In the example above, row 5 should be invalid because source_id = 2002 was already used once with org_id = 22, and it should not be possible to use it again with org_id = 11.

I know how to achieve this in a query, but it either requires locking, or it requires a multi-step process in application code (which wouldn't be the end of the world). I thought it could be done with an EXCLUDE constraint, but I'm not sure.

You don't need to create this contraint artificially. Just promote your database model from 2NF to 3NF and this issue will be gone for good. Currently you have inter-column dependency, and 3NF takes care of that. — The Impaler, Feb 02 '22 at 02:37
You can find a solution with `exclude` in this answer: [Postgres index with only a single combination of integers](https://stackoverflow.com/a/51118472/1995738) — klin, Feb 02 '22 at 02:46
https://stackoverflow.com/questions/51247063/what-does-exclusion-constraint-exclude-using-gist-c-with-mean/51247705 maybe helpful. — jian, Feb 02 '22 at 03:48
@TheImpaler can you post that as an answer, with more details? — tompave, Feb 02 '22 at 14:57

score 2 · Accepted Answer · 2022-02-02T03:36:18.973

This might do the trick:

CREATE TABLE resources (
  id         SERIAL      PRIMARY KEY,
  org_id     INT         NOT NULL,
  source_id  INT         NOT NULL,
  data       JSON        NOT NULL,
  EXCLUDE USING gist
  (org_id WITH =,
  source_id WITH <>)
);

It works correctly with your example: dbfiddle

Explanation of <> operator is here

Example usage of = and <> operators is here

score 1 · Answer 2 · answered Feb 02 '22 at 15:05

1

You current database model is in 2NF. To promote it 3NF you need to break the table in two tables as explained in Third normal form -- Wikipedia.

To promote it to 3NF you can do:

CREATE TABLE organization (
  org_id     INT         PRIMARY KEY NOT NULL,
  source_id  INT         NOT NULL
);

CREATE TABLE resources (
  id         SERIAL      PRIMARY KEY,
  org_id     INT         NOT NULL REFERENCES organization (org_id)
  data       JSON        NOT NULL
);

This way the table resources won't include values for the column source_id anymore, and there won't be any possibility of producing inconsistent data whatsoever. Now, source_id will safely live in the table organization instead, where only a single value per organization is possible.

As you see, this is a structural solution that is also quite clean.

answered Feb 02 '22 at 15:05

The Impaler

45,731
9
39
76

@tompave I would like to ask you about your gut reaction to this solution. Is it something you consider good, just decent, or maybe totally unrealistic to your reality? Please comment. No need to respond in an elaborate way but I want your feeback on this. – The Impaler Feb 02 '22 at 15:45
Thank you for this. I can see how it would help with the example in my question, but unfortunately I cannot change the DB schema that much. In my case, `org_id` and `souce_id` are not FKs to other tables in the dame DB, and they're just references to some remote resources. I named them `*_id` just to keep the example simple, but now I see how that was misleading, sorry. I suppose that I could add a minimal `organizations` table like how you're showing me, but as I said I'm not sure I can change the schema that much. – tompave Feb 07 '22 at 01:10

SQL: table constraint to reject rows based on table contents

2 Answers2