1

Can I create a constraint on a database that ensures some relationship between columns in different tables linked by a foreign key? So, as an example, can I ensure that a row in a table has a value for a certain column that is larger than the value in a column in a row in another table that it references.

So naively you might think it would look like:

CREATE table1 (
    id integer PRIMARY KEY,
    col1 integer
);
CREATE table2 (
    id integer PRIMARY KEY,
    link_to_1 integer REFERENCES table1,
    col2 integer,
    -- Is there some way of specifying the following constraint?
    CHECK (col2 > link_to_1.col1)  
);

So a row in table1 can never have an entry with a col1 greater than the col2 value in the link_to_1 row.

Obviously this constraint would therefore prevent you from subsequently modifying the table1.col1 value to be a higher value than the table2.col2 value.

daphtdazz
  • 7,754
  • 34
  • 54
  • Look up constraint triggers. – sticky bit Jan 10 '19 at 14:06
  • I don't think this can be done easily. – Laurenz Albe Jan 10 '19 at 14:09
  • @stickybit I looked up constraint triggers, but they just seemed to be normal triggers that you could defer like constraints to the end of the transaction, so don't seem to solve the problem. Did you have something else in mind? – daphtdazz Jan 10 '19 at 15:42
  • 1
    @daphtdazz maybe this thread will work for you: https://stackoverflow.com/questions/27107034/constraint-to-check-values-from-a-remotely-related-table-via-join-etc – Ariel Jan 10 '19 at 17:39
  • Look at the link @daphtdazz has posted. That was exactly what I meant. In contrast to a constraint, in a trigger you can query other tables and raise an error if you don't like what you get. – sticky bit Jan 10 '19 at 22:46

0 Answers0