CODEWITHSUNDEEP
postgresqlconstraintsunique-constraint
FableBlaze
FableBlaze

Reputation: 1895

Can i set values to only be allowed in a single "group"

I have a table similar to that looks something like this:

 code | group
------+------
  1   |   a
  1   |   a
  2   |   a
  9   |   b
  9   |   b
  8   |   b

Is there a way to set a constraint so that the same code can appear only in one group?

So that for example adding a row code=3; group=a would not violate the constraint, but adding a row code=1; group=b would be a violation of the constraint.

Upvotes: 0

Views: 26

Answers (1)

oᴉɹǝɥɔ
oᴉɹǝɥɔ

Reputation: 2075

I don't believe you can do it if these fields are in the same table. It looks to me that you have a hierarchy in which group is the master level and code is detail. I see this can be solved with 3 tables:

CREATE TABLE "group" (
    id "char" CONSTRAINT "group_pk" PRIMARY KEY
)

CREATE TABLE "code" (
    id INTEGER CONSTRAINT "code_pk" PRIMARY KEY,
    group_id "char",
    FOREIGN KEY group_id REFERENCES "group" (id)
)

CREATE TABLE "data" (
    id INTEGER CONSTRAINT "data_pk" PRIMARY KEY,
    code_id "char",
    ...
    FOREIGN KEY code_id REFERENCES "code" (id)
)

where data table represents the table you are talking about.

Upvotes: 1

Related Questions