How to create a case-insensitive unique constraint?
Hi!
If I try something like
ALTER TABLE foo
ADD CONSTRAINT foo_unique_xy
UNIQUE ( UPPER( x ), UPPER( y ) );
...I get a syntax error
ERROR: syntax error at or near "("
LINE 3: UNIQUE ( UPPER( x ), UPPER( y ) );
Is there a way to do this?
TIA!
Kynn
You can use a unique index:
CREATE UNIQUE INDEX idx_foo_unique_upper_x_upper_y ON foo
(UPPER(x), UPPER(y));
"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