CODEWITHSUNDEEP
sqlpostgresql
Sankar
Sankar

Reputation: 6522

postgresql transitive unique constraints via foreign key

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.

Upvotes: 1

Views: 883

Answers (1)

The Impaler
The Impaler

Reputation: 48770

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

Upvotes: 1

Related Questions