sqloracle10gunique-constraint

Create Unqiue case-insensitive constraint on two varchar fields

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"
Solution

  • 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.