Thread: How to create a case-insensitive unique constraint?

"Kynn Jones" <kynnjo@gmail.com> writes:
> ALTER TABLE foo
>   ADD CONSTRAINT foo_unique_xy
>   UNIQUE ( UPPER( x ), UPPER( y ) );

> ...I get a syntax error

This is disallowed by the SQL standard: UNIQUE constraints can only be
on plain columns.  (The practical reason for following their rule is
that there'd be no way to represent more-general constraints in the
information_schema.)

Use the CREATE UNIQUE INDEX syntax instead.

            regards, tom lane

You can use a unique index:

CREATE UNIQUE INDEX idx_foo_unique_upper_x_upper_y ON foo
    (UPPER(x), UPPER(y));