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)
);
Kinnard Hockenhull
  • 165
  • 1
  • 5

1 Answers1

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) ) 
);
a_horse_with_no_name
  • 69,148
  • 13
  • 135
  • 173
  • 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? – Kinnard Hockenhull Nov 17 '14 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. – a_horse_with_no_name Nov 17 '14 at 22:50
  • it's clear to me why it could be redundant, not that it definitely is. I get the drift though :) – Kinnard Hockenhull Nov 17 '14 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. – a_horse_with_no_name Nov 17 '14 at 23:02