6

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)
);

1 Answer 1

7

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) ) 
);
4
  • 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? Nov 17, 2014 at 22:48
  • @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.
    – user1822
    Nov 17, 2014 at 22:50
  • it's clear to me why it could be redundant, not that it definitely is. I get the drift though :) Nov 17, 2014 at 23:00
  • 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.
    – user1822
    Nov 17, 2014 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.