Constraint for nullable column involving column of another table

Question

I have two tables:

table1:
id bigserial not null,
value1 integer not null

table2:
id bigserial not null,
table1_id bigint not null,(FK)
value2 character varying not null,
value3 character varying,

I need to add some constraint to be sure that when value3 is not null, then combination of value3, value2, and table1.value1 should be unique per table2. How can I do it?

Your Postgres version? And `table1.value1` is not defined UNIQUE? (`table1.id` obviously requires a PK or UNIQUE constraint to allow the FK.) Write patterns? Is `table1` read-only? — Erwin Brandstetter, Jan 08 '18 at 16:35
table1.value1 is not unique. Table1.id is a PK. Postgres version 9.4 — Mindro, Jan 08 '18 at 16:45
Please [edit] the question to present complete information. Comments are not the place. — Erwin Brandstetter, Jan 08 '18 at 17:48

Erwin Brandstetter · Answer 1 · 2018-01-08T18:06:17.210

For starters you could have a partial UNIQUE index

CREATE UNIQUE INDEX table2_foo_idx ON table2 (value3, value2, table1_id)`
WHERE value3 IS NOT NULL;

But this does not account for duplicate values of value1 in table1. This cannot be enforced with a constraint without dirty tricks, since all relevant constraints only involve columns of the same table.

There are various workarounds, depending on the complete situation. Triggers, a partial functional unique multicolumn index using a fake immutable function, a (NOT VALID) CHECK constraint ...

One clean way to enforce this is to store value1 in table2 redundantly, add multicolumn FK constraint with ON UPDATE CASCADE and create the unique index mentioned above:

CREATE TABLE table1 (
   table1_id bigserial PRIMARY KEY
 , value1    text NOT NULL
 , UNIQUE   (value1, table1_id)  -- logically redundant, neede for FK
);

CREATE TABLE table2 (
   table2_id bigserial PRIMARY KEY
 , table1_id bigint NOT NULL
 , value1    text NOT NULL
 , value2    text NOT NULL
 , value3    text
 , FOREIGN KEY (table1_id, value1) REFERENCES table1(table1_id, value1) ON UPDATE CASCADE
);

CREATE UNIQUE INDEX table2_foo_idx ON table2 (value3, value2, value1)`
WHERE value3 IS NOT NULL;

Is a composite index also good for queries on the first field?

Value1 in table1 can be duplicate – Mindro Jan 08 '18 at 16:48 — Mindro, Jan 08 '18 at 16:48

Constraint for nullable column involving column of another table

1 Answers1