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)
);
Best Answer
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
thenrefund_id
must reference an existing transaction".I don't think you need the
refunded
column at all. Because the flagrefunded
can be derived from the value of therefund_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: