Michael Norton Michael Norton - 1 year ago 69
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
isn't in the
column for a specific person. (null
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 Source

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.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download