Question 289924 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres – how to add foreign key nullable constraint for two different columns coming from diff tables

I have three tables 1. Account 2. dept and 3. managers
In the managers table I need to add both account key and dept key as foreign key but one will null and other will have value, means if inserted account manager then dept key will null and account key will have value. how to add constraint in postgres

Tags:

2

Answers


  1. 0

    Demo at db<>fiddle

    create table account(id serial primary key);
insert into account values (default);

create table dept(id serial primary key);
insert into dept values (default);

create table manager(
  id serial primary key,
  account_id int references account(id),
  dept_id int references dept(id) );

    One null, one not null

    If you always want exactly one of those foreign keys to be null and the other not null, you can use a XOR. It won’t allow both to have a value, and it will also not allow a row where they are both null:

    alter table manager add constraint dept_xor_account
  check( (account_id is null) != (dept_id is null) );

    There’s a XOR # operator, but only for type bit, so it’s not really worth all the casting:

    alter table manager add constraint dept_xor_account
  check(((account_id is null)::int::bit # (dept_id is null)::int::bit)::int::bool);

    insert into manager values(default,1,null);--works fine
insert into manager values(default,null,1);--works fine

insert into manager values(default,1,1);--can't have both links

    ERROR:  new row for relation "manager" violates check constraint "dept_xor_account"
DETAIL:  Failing row contains (3, 1, 1).

    insert into manager values(default,null,null);--can't have neither link

    ERROR:  new row for relation "manager" violates check constraint "dept_xor_account"
DETAIL:  Failing row contains (4, null, null).

    At most one null

    If you allow at most one to be null (one or both can be null), you can count them:

    alter table manager add constraint dept_or_account_or_neither
  check(((account_id is not null)::int+(dept_id is not null)::int)<2);

insert into manager values(default,1,null);--works fine
insert into manager values(default,null,1);--works fine
insert into manager values(default,null,null);--works fine, CAN have neither link

insert into manager values(default,1,1);--can't have both links

    ERROR:  new row for relation "manager" violates check constraint "dept_or_account_or_neither"
DETAIL:  Failing row contains (8, 1, 1).

    fiddle

    Login or Signup to reply.
  2. 0

    Your description indicates yo are not looking for at most 1 null, but instead looking for exactly 1 null. You determine this with the num_nulls() function incorporated into a check constraint. So (see demo)

    create table manager(
  id serial primary key,
  account_id int references account(id),
  dept_id int references dept(id),
  constraint dept_xor_account check ( num_nulls(account_id,dept_id) = 1);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search