Michael Norton Michael Norton - 10 days ago 5
SQL Question

postgres: Check pair of columns when inserting new row

I have a table like this:

id | person | supporter | referredby|
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
0 | ABC | DEF | |
1 | ABC | GHI | DEF |
2 | CBA | FED | |
3 | CBA | IHG | FED |


What I'm trying to accomplish is I'd like postgres to reject an INSERT if the value in
referredby
isn't in the
supporter
column for a specific person. (null
referredby
is ok)

For example, with the data above:


  • 4, 'ABC', 'JKL', null
    : accepted (can be null)

  • 4, 'ABC', 'JKL', 'IHG'
    : rejected (IHG not listed as a supporter for ABC)

  • 4, 'ABC', 'JKL', 'DEF'
    : accepted (DEF is listed as a supporter for ABC)



Maybe a check constraint? I'm not sure how to piece it together

Answer

Add a foreign key that references person, supporter. (Needs to be unique key.)

alter table t add constraint cname unique(person, supporter);

alter table t add constraint fk foreign key (person, referredby)
    references t (person, supporter);

(ANSI SQL syntax, but probably also supported by Postgresql.)