Saturday, December 22, 2007

if you have many foreign keys, how to check that only one of them must not be null

create table shipment
int seq serial bigint not null


date_to_ship date,

constraint fk_shipment__client foreign key(client_code)
references client(client_code),

constraint fk_shipment__supplier foreign key(supplier_code)
references supplier(supplier_code),

constraint fk_shipment__testing_company foreign key(testing_company_code)
references testing_company(testing_company_code),

constraint fk_shipment__branch foreign key(branch_code)
references branch(branch_code),

constraint ck_shipment__company check
(client_code is not null)::int
(supplier_code is not null)::int
(testing_company_code is not null)::int
(branch_code is not null)::int
) = 1


Note: if you only need to test at least one of them is not null, just change = 1 to >= 1.

or better yet, do this:

constraint ck_shipment__company

check(coalesce(client_code, supplier_code, testing_company_code, branch_code) is not null)

Labels: ,


Post a Comment

Links to this post:

Create a Link

<< Home