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

client_code,
supplier_code,
testing_company_code,
branch_code,

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: ,

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home