Search code examples
sqlpostgresqlcreate-tableunique-constraint

Unique constraint on two columns irrespective of order

I have a pretty simple table:

    id  | name   | alternate
--------+--------+------------
     1  |   Joe  |     Joseph
--------+--------+------------
     2  |   Pete |     Peter
--------+--------+------------

and so on.

I want to add a constraint on the name and alternate column, but irrespective of order. For example, I don't want to be able to insert (3, 'Peter', 'Pete') as that is essentially the same as id 2, just with the columns reversed.

Is there a way to do this?

Solution

  • Create a unique index:

    CREATE UNIQUE INDEX ON atable
   (LEAST(name, alternate), GREATEST(name, alternate));