Create Unqiue case-insensitive constraint on two varchar fields

Question

In Oracle 10g, how do I add a unique case-insensitive constraint on two varchar fields? For example, given the following records already in the table:

"Stephen", "Swensen"
"John", "Smith"

The following inserts would be invalid:

"stephen", "Swensen"
"John", "smith"
"stephen", "swensen"

But the following inserts would be valid:

"Stephen", "Smith"
"John", "Swensen"

score 12 · Answer 1 · answered Apr 04 '13 at 02:35

12

I've managed to get it working by doing:

CREATE UNIQUE INDEX person_name_upper ON person(
    UPPER(first_name), UPPER(last_name));

answered Apr 04 '13 at 02:35

priomsrb

2,602
3
26
34

using postgresql 9.4, this is the solution that worked for me.michael's accepted solution above threw a syntax exception, not liking the `lower` keyword inside the `unique` declaration. – liltitus27 Dec 20 '16 at 15:19

Michael Goldshteyn · Accepted Answer · 2010-10-15T18:19:52.730

7

Assuming your table is called person, and the first and last name columns are called first_name and last_name, add this unique constraint:

ALTER TABLE person ADD CONSTRAINT person_name_unique
    UNIQUE(LOWER(first_name),LOWER(last_name));

Let me know if I understood your question correctly and made the correct assumptions about your table layout.

edited Oct 15 '10 at 18:19

answered Oct 15 '10 at 18:09

Michael Goldshteyn

71,784
24
131
181

2

I don't think `LOWER` belongs in a constraint like that. It's better to specify a collation. – Gabe Oct 15 '10 at 18:23
1

You got it, thanks! I also found this, which works as well: create unique index UK_U_FIRSTNAME_LASTNAME on PERSON (LOWER(FIRST_NAME), LOWER(LAST_NAME)); – Stephen Swensen Oct 15 '10 at 18:24
4

LOWER(COL_NAME) is not working inside UNIQUE. Gives error SQL Error: ORA-00904: : invalid identifier – Mukesh Singh Rathaur Jul 02 '15 at 12:44

Create Unqiue case-insensitive constraint on two varchar fields

2 Answers2

Linked