how can unique constraint modified in postgresql for column 'A' not unique over same value in column B and unique over different values for column B?

Question

PROBLEM: In a table; column 1 is not unique for same value of column 2 but column 1 value is unique over different values of column 2. How can this condition be implemented in postgreSQL db.

Explaination

Assume table 1 exists in DB

Table 1

Column A	Column B	Extra
One	apple	green
One	apple	blue
Two	apple	pink
Three	orange	purple

for Column B as "apple" if in Column A value "One" exists, then for any other value of Column B, Column A will not have "One".
Example: element from Table 2 Should NOT Exist

Table2

Column A	Column B	Extra
One	orange	red

**the Extra column can have any value

if you want to forbid/exclude values that exist in **an other table**: that can only be done via a trigger. — wildplasser, Jan 25 '22 at 11:27
This is a bad design & is the sort of design that DB normalization to higher NFs gets rid of. — philipxy, Jan 25 '22 at 11:35

score 0 · Answer 1 · answered Jan 25 '22 at 12:29

You can solve the problem by properly normalizing your data model:


CREATE TABLE part_2 (
   column_a text PRIMARY KEY,
   column_b text NOT NULL
)

CREATE TABLE part_2 (
   column_a text REFERENCES part_2,
   extra text NOT NULL
);

(part2 is still missing a primary key.)

Then your table would be split up like this:

part_1:

column_a	column_b
One	apple
Two	apple
Three	orange

part_2:

column_a	extra
One	green
One	blue
Two	pink
Three	purple

Now it is technically impossible to add data where column_a is identical but column_b is different, and your problem is solved.

If you want to retain your original table format, use a view:

CREATE VIEW total AS
SELECT column_a, part_1.column_b, part_2.extra
FROM part_1 JOIN part_2 USING (column_a);

how can unique constraint modified in postgresql for column 'A' not unique over same value in column B and unique over different values for column B?

Explaination

Table 1

Table2

1 Answers1