postgresql transitive unique constraints via foreign key

Question

I have the following schema:

CREATE TABLE t2 (
    id TEXT PRIMARY KEY,

    t1_id TEXT REFERENCES t1(id)
);

CREATE TABLE t3 (
    id TEXT PRIMARY KEY,

    t2_id TEXT REFERENCES t2(id)
);

CREATE TABLE t4 (
    t4_counter BIGINT NOT NULL,

    t3_id TEXT REFERENCES t3(id),

    // Need UNIQUE constraint on t4_counter + t2_id
);

There are 3 postgresql tables (t2, t3, t4) in our system. There are other tables but they are ignored for brevity. Now t2 has an UNIQUE id which is referred in t3 as a FOREIGN KEY. Similarly t3 has an UNIQUE id that is referenced as a FOREIGN KEY in t4. Now in t4 there is a field called t4_counter. Now I want to apply a UNIQUE constraint on this table t4, such that, t4_counter combined with t2_id will always be UNIQUE.

One easy way to achieve this will be insert t2_id as a new column with a REFERENCES constraint in the table t4 and then add a UNIQUE constraint on (t2_id, t4_counter). But since we already have the t3_id in the table t4 we should be able to calculate it transitively.

Is it possible to achieve such a unique constraint without adding t2_id in the table t4 ?

I am using postgresql 12+ versions if it matters.

Because of some business logic related constraints. But that should not affect anything here iiuc, right ? — Sankar
– Sankar, Commented Mar 28, 2020 at 8:34
I think adding t2_id to t4 is the easiest way. You can't have an index (unique constraint) on multiple tables. This thread from 2011 but still relevant (see responses): postgresql.org/message-id/… — Jindra
– Jindra, Commented Mar 28, 2020 at 8:43
Thanks @Jindra I guess that is probably the solution. I too can't figure out anything from google and so asked. — Sankar
– Sankar, Commented Mar 28, 2020 at 8:56
If a column is not present on a table you can't produce a foreign key. You'll need to add the column t2_id as "redundancy", as @jindra mentions, in order to enforce this relationship. You could also use a composite key for table t4. That would solve the problem at the database level for good. — The Impaler
– The Impaler, Commented Mar 28, 2020 at 11:27

The Impaler · Accepted Answer · 2020-03-28 11:32:35Z

1

You'll need to add the column t2_id as "redundancy", as @jindra mentions, in order to enforce this relationship. You could also use a composite key for table t4. That would solve the problem at the database level for good.

For example:

create table t3 (
  id int not null,
  t2_id int not null references t2(id),
  primary key (id, t2_id) -- composite primary key
);

create table t4 (
  t4_counter bigint not null,

  t3_id int not null,
  t2_id int not null,
  constraint fk1 foreign key (t3_id, t2_id) references t3 (id, t2_id),

  constraint uq1 unique (t2_counter, t2_id)
);

answered Mar 28, 2020 at 11:32

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql transitive unique constraints via foreign key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related