CODEWITHSUNDEEP
sqlpostgresql
Spongerooski
Spongerooski

Reputation: 33

SQL : unique values across two columns

Is there a constraint for values not being unique taking in consideration two columns, ex -

id  |  secondid
+---------------+
 3  |         4
 4  |         5

id  |  secondid
+---------------+
 3  |         4
 5  |         4

id  |  secondid
+---------------+
 4  |         4
 4  |         4

All the above cases are not okay, as 4 occurs twice in either id or secondid but something like

id  |  secondid
+---------------+
 1  |         3
 2  |         4

is okay as all the values in both the columns are unique, is there any way for me to achieve this without using any packages in postgresql?

Upvotes: 3

Views: 228

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

You can do this with a combination of an exclusion constraint and a check constraint. The check constraint is needed to prevent duplicates within one row.

create table t (
    id int,
    id2 int,
    check (id <> id2),
    exclude using gist ( (array[id, id2]) with &&)
);

The exclusion constraint operates by checking the specified operator never returns "true" for the column in the "new" row and all rows already in the table. It does not check values within the current row, which is why the check constraint is also needed.

Here is a db<>fiddle.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want a unique constraint that works on the two columns as if these were just one column. I think this is not possible directly. (Others may correct me.)

What you can do is create another table

create table check_unique_id (id int primary key);

and fill it via a trigger. I.e. every time you insert a row in your table, the trigger creates two rows in the check_unique_id table. If an ID occurs twice that other table will raise the exception.

Upvotes: 1

Related Questions