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?

Answer 1

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.