Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL)

Question

Loan table associated to due table.

also

Invoice table associated to due table.

In due table either one of them foreign key should present and other one should be null.

What kind of constraint will be perfect ?

Thanks.

Answer 1

As it sounds, you cannot solve this with one constraint, but nothing prevents you from defining more of them. I believe something like this should work:

CREATE TABLE due (
    ...,
    d_invoice_id REFERENCES invoice (i_id),
    d_loan_id REFERENCES loan (l_id),
    ...,
    CHECK (   (d_invoice_id IS NULL AND d_loan_id IS NOT NULL) 
           OR (d_invoice_id IS NOT NULL AND d_loan_id IS NULL))
);

A column that has a foreign key defined on does not have to be NOT NULL, unlike the column it references. Apart from that, you may define checks of arbitrary complexity - it is advisable to keep complexity at its necessary minimum, though.

Answer 2

I believe you cannot have such a contraint. You may have a column contraint or a table constraint. When working on foreign keys, they are on the table that "points" to the other table, and cannot use columns of other tables. So a constraint on table invoice can only check for invoice columns and cannot access loan columns.

You might work around the problem using triggers on both load and invoice tables. A trigger is function and can operate on any other table, beside the one it is attached.