SQL : unique values across two columns

Question

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?

@mu is too short: The other request is about tuples. They want to prevent (1,2) and (2,1) from both being in the table, because this is the same tuple, only with the values reversed. Spongerooski's request, however, is about single IDs. They want to prevent (1,2), (3,2) for instance, because an ID occurs more than once. — Thorsten Kettner
– Thorsten Kettner, Commented Jul 10, 2021 at 6:45
@ThorstenKettner Thanks, a second reading made me think it was more interesting than I first thought. — mu is too short
– mu is too short, Commented Jul 10, 2021 at 7:35
@Spongerooski . . . I suspect that something is wrong with your data model if you need to do this. You seem to have one id. There should be a table where that value is in one column. — Gordon Linoff
– Gordon Linoff, Commented Jul 10, 2021 at 11:51
Gordon has a point there. The real problem and hence the solution may really lie in the data model. You should really consider this (rather than just going with Gordon's answer, which perfectly solves the problem technically). — Thorsten Kettner
– Thorsten Kettner, Commented Jul 10, 2021 at 12:14

Gordon Linoff · Accepted Answer · 2021-07-10 12:03:21Z

2

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.

answered Jul 10, 2021 at 12:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spongerooski Over a year ago

I seem to be getting an error data type integer[] has no default operator class for access method "gist", do I need to have any extension for this?

Gordon Linoff Over a year ago

@Spongerooski . . . postgresql.org/docs/current/intarray.html.

Thorsten Kettner · Accepted Answer · 2021-07-10 06:42:26Z

1

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.

answered Jul 10, 2021 at 6:42

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

3 Comments

Spongerooski Over a year ago

I was hoping there was a direct way, this seems a bit hacky in my opinion but I'll definitely go with this if there isn't any other way, thank you :D

mu is too short Over a year ago

I think you can use an EXCLUDE constraint for this. If you CREATE EXTENSION intarray and then exclude using gist ((array[id, secondid]) with &&).

Thorsten Kettner Over a year ago

@mu is too short: Clever. Gordon has posted an answer using the same idea. Seems to work perfectly.

Collectives™ on Stack Overflow

SQL : unique values across two columns

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related