CODEWITHSUNDEEP
sqlpostgresqlunique-constraint
Hari
Hari

Reputation: 333

pgsql how to enforce uniqueness for some keys

I have two tables. table1 has attributes unique or not

attribute_id     uniqueness 
   1              no
   2             yes
   3             yes

Table 2 has below data, there is unique constraint on combined columns object_id and attribute_id . Now I need another unique constraint on value column for some attribute_id has uniqueness=yes.

Like, attribute_id 2 and 3 has unique=yes in table1. they can't have duplicate values in table2.

auto_id  object_id   attribute_id    value
  1         1           1             a
  2         2           1             a
  3         1           2             b
  4         2           2             d

We can't insert (attribute_id,value)=(2,b) or (2,d) and it should through error duplicate key value violates unique constraint if we try to insert dup values.

I hope we can do this by trigger but it will be very slow.

Looking for better approach, any possible solution pls ?

Upvotes: 1

Views: 155

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

The only way to do that with a constraint that I can think of is this:

  • add the uniqueness column to table2 too and fill it with the proper value

  • define a UNIQUE constraint on table1(attribute_id, uniqueness)

  • define a foreign key constraint on table2:

    ALTER TABLE table2 ADD FOREIGN KEY (attribute_id, uniqueness)
   REFERENCES table1 (attribute_id, uniqueness);

  • define a partial unique index on table2:

    CREATE UNIQUE INDEX ON table2 (attribute_id, value) WHERE uniqueness;

Of course this will lead to data duplication, but the foreign key will guarantee that no inconsistencies can occur.

Upvotes: 1

Related Questions