Change constraint on column based on value of another

Question

Is it possible to change a constraint on a column in postgres based on the value of another column? E.g. (pseudocode):

CREATE TABLE transactions(
 id SERIAL PRIMARY KEY,
 type TXN_TYPE NOT NULL,
 amount BIGINT,
 . . . .,
 refunded boolean DEFAULT FALSE,
 refund_id DEFAULT NULL if (CONSTRAINT link_refund CHECK (refunded=TRUE))=TRUE REFERENCES transactions(id)
);

Answer 1

A foreign key can not be "conditional". The only "exception" to that rule are null values which can't reference another table by definition.

If I understand your question correctly, you are trying to implement a constraint that says "if refunded is true then refund_id must reference an existing transaction".

I don't think you need the refunded column at all. Because the flag refunded can be derived from the value of the refund_id using the expression: refund_id is not null.

If you do want a column like that, just create a view with that expression.

If you do insist on having the redundant refunded flag, you could setup a check constraint like this:

CREATE TABLE transactions(
 id SERIAL PRIMARY KEY,
 type TXN_TYPE NOT NULL,
 amount BIGINT,
 . . . .,
 refunded boolean DEFAULT FALSE,
 refund_id integer null REFERENCES transactions,
 constraint check_refund 
 check ( (refunded and refund_id is not null or
 (not refunded and refund_id is null) ) 
);

• Kinnard Hockenhull: Why can one be certain that it is redundant? Also a refund_id integer NULL REFERENCES transactions by itself will default to null AND constrain values to transactions(id), correct?
• user1822: @KinnardHockenhull: why it is redundant? Well if refunded is true, apparently refund_id must be not null and therefor it points to a valid transaction. If refunded is false, refund_id should be null. Unless the last assumption is not correct, the value of refunded can be deduced by the fact that a refund_id exists. And yes, that's how foreign keys work.
• Kinnard Hockenhull: it's clear to me why it could be redundant, not that it definitely is. I get the drift though :)
• user1822: If you allow refunded = true AND refund_id IS NULL then (and only then) it would not be redundant. But that would seem like a strange combination to allow.