Add constraint to ensure a common parent of two foreign key

Question

I am having trouble adding a check constraint to a table.

Here is the context.

"Account" and "Category" are two tables each related by a foreign key to the table "Budget" but are otherwise independant
table "Operation" is related to both "Account" and "Category" by two foreign keys

=> I would like to be sure that they both reference the same item of "Budget" so that : for each operation, account.budget_id == category.budget_id.

How can I manage that with a constraint on my database ? or is there a way to prevent this diamond shape in my database design while keeping account and category independant ?

What I tried :

CONSTRAINT category_and_account_are_from_same_budget CHECK
(
    (
        SELECT cat.budget_id FROM operation op INNER JOIN category cat ON (op.category_id=cat.id)
        except
        SELECT acc.budget_id FROM operation op INNER JOIN account acc ON (op.account_id=acc.id)
    ) IS NULL
)

but

that didn't feel like an elegant way to do it
and anyway... "ERROR: cannot use subquery in check constraint"

thanks for your help !

Shouldn't `buget_id` be stored in the `operation` table to start with? — GMB, Sep 14 '20 at 10:11
@GMB Thanks for your comment, Well it could be, would that prevent the problem ? there is no use for it otherwise and since it can be inferred by both account and category I tought it would only add redondancy. — Emarco, Sep 14 '20 at 10:13
@MikeOrganek thanks, I didn't have the right keyword but it seems similar indeed, I will try their answer. — Emarco, Sep 14 '20 at 10:18
@Emarco I have no idea what the keyword should be for this. I gave you the link because I just answered it a couple of days ago. I have encountered the problem numerous times, and my usual advice is to make sure your middleware code is correct. It never occurred to me to use an external check constraint this way. Checks like that are assumed to be bad practice, but given that these are all FK constraints, it seems safe enough, and the check provides better integrity than without it. — Mike Organek, Sep 14 '20 at 10:28
@MikeOrganek thanks, it worked perfectly ! I will ensure my code is right but I feel better knowing the database won't let it go wrong. Why are the check considered a bad pratice ? I'm currently refining my design by adding a few so I would be interested to know if I shouldn't. — Emarco, Sep 14 '20 at 10:36
@Emarco Please see the first note (in blue) on this page: https://www.postgresql.org/docs/current/ddl-constraints.html So long as the logical design of your application will not be moving `category` or `account` rows to different `budget` values, then I think there is no problem with the stored function approach. Gordon's answer, below, is technically more correct but leads to redundancy as he notes. — Mike Organek, Sep 14 '20 at 10:45

score 1 · Answer 1 · answered Sep 14 '20 at 10:30

1

The simplest way, in my opinion, is to do the following:

Add budget_id to operation.
Add unique constraints to account and category on two columns: budget_id and id. This constraint is redundant but needed for the next step.
Define the foreign keys in operation using the (budget_id, id) unique constraint.

This guarantees that they budgets are the same.

There is an alternative to this. You can use a check constraint. However, that requires writing two user-defined functions to return the budget_id for each account and category.

answered Sep 14 '20 at 10:30

Gordon Linoff

1,242,037
58
646
786

thanks ! I didn't understood why adding budget_id to operation as suggested in the comments would help and your answer is very clear. For this case though I prefer to use a external function as suggested by @MikeOrganek in the comments but your answer is sure to be useful to others ! – Emarco Sep 14 '20 at 10:42
@Emarco . . . Interesting. I prefer to just fiddle with the constraints on the tables rather than adding a new function. But that is a matter of preference. – Gordon Linoff Sep 14 '20 at 10:48

score 0 · Accepted Answer · answered Sep 24 '20 at 10:44

I chose to implement the solution suggested by Mike Organek in the comments (putting it here for more visibility and the concrete implementation of this answer)

create or replace function are_category_and_account_from_same_budget (_category_id int, _accound_id int)
  returns boolean as $$
  select count(*) = 1
    from account acc
       join category cat
         on cat.budget_id = acc.budget_id
    where cat.id = _category_id
       and acc.id = _accound_id;
$$ language sql;

CREATE TABLE operation (
  ...
  account_id INT NOT NULL,
  category_id INT NOT NULL,
  FOREIGN KEY (account_id) REFERENCES account(id),
  FOREIGN KEY (category_id) REFERENCES category(id),

  -- BEWARE - this constraint will only prevent the creation of a wrong operation but if the budget_id is changed afterward, it won't be noticed.
  CONSTRAINT category_and_account_are_from_same_budget CHECK (are_category_and_account_from_same_budget(category_id, account_id)));

Beware Using an external function won't insure a complete compliance of your database (cf postgresql documentation referenced by Mike Organek)

For exemple :

if I try to create an operation related to an account (whose budget_id is "1") and a category (whose budget_id is "1" also) => it works
if I try to create an operation related to an account (whose budget_id is "1") and a category (whose budget_id is "3") => it doesn't works

So I'm covered when it come to creating/updating operation BUT

if I try to create an operation related to an account (whose budget_id is "1") and a category (whose budget_id is "1") => it works
Then I update the budget_id of my category to "3" => it works also.

It isn't a problem for me, since categories and accounts are never update (only created and deleted) so I would rather have this loophole than add to many redondancy to my db design. But if it is a problem for you, you should rather use @Gordon Linoff answer.

Add constraint to ensure a common parent of two foreign key

What I tried :

2 Answers2