i'm trying to create a table with 2 int-columns and a constraint that a pair of (x,y) cannot be as (y,x) inserted:
test=# create table foo(u1 int,u2 int, unique (least(u1,u2),greatest(u1,u2))); ERROR: syntax error at or near "(" LINE 1: create table foo(u1 int,u2 int, unique (least(u1,u2),greates...
I know, i can solve that in this way:
test=*# create table foo(u1 int,u2 int); CREATE TABLE test=*# create unique index idx_foo on foo(least(u1,u2),greatest(u1,u2)); CREATE INDEX
But is there a way to define the unique constraint within the create table - command?
You might consider adding a table constraint "u1 <= u2". You could also consider adding a trigger that munges data on INSERT/UPDATE so that this constraint is met regardless of the order supplied by the user.
