Add constraint to ensure a common parent of two foreign key

user_2933593 0
Viewed 269

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.

database diagram of my current situation

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

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

thanks for your help !

postgresql sql
2 Answers

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

  1. Add budget_id to operation.
  2. Add unique constraints to account and category on two columns: budget_id and id. This constraint is redundant but needed for the next step.
  3. 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.

user_11440350

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.

user_29335930
Related