guarantee case insensitive uniqueness
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
As Solution Using Function Based Indexes
A common issue, when building applications, is a request from our customers to allow them to insert their data any way they want but to guarantee case insensitive uniqueness. Here's an example:
CREATE TABLE depts (
dept_id   VARCHAR2(2),
dept_name VARCHAR2(30));

ALTER TABLE depts
ADD CONSTRAINT pk_departments
PRIMARY KEY (dept_id);

ALTER TABLE depts
ADD CONSTRAINT uc_dept_name
UNIQUE (dept_name);

INSERT INTO depts (dept_id, dept_name) VALUES ('1', 'Accounting');
INSERT INTO depts (dept_id, dept_name) VALUES ('2', 'RESEARCH');
INSERT INTO depts (dept_id, dept_name) VALUES ('3', 'Sales');

SELECT * FROM depts;
So far I have a happy customer. My primary key guarantees that each department identifier (1, 2, 3) is unique. But how do I prevent my customers from accidentally doing this?
INSERT INTO depts (dept_id, dept_name) VALUES ('4', 'Research');
INSERT INTO depts (dept_id, dept_name) VALUES ('5', 'research');
INSERT INTO depts (dept_id, dept_name) VALUES ('6', 'ResearcH');

SELECT * FROM depts;
and we know that sooner, not later, they will.

Notice how departments 2 and 4 violate the customer's requirement, by allowing the research department to be entered twice, while not violating the unique constraint. Each one, from the computer's standpoint is unique.

I try to could solve the problem with a trigger by forcing all entries into upper, lower, or initcap case but triggers are evil (see Tom Kyte's commments on the subject) and that does not meet the customer's requirement.

One solution is to leverage the power of function based indexes. We will drop the zero-value unique constraint and replace it with an FBI. The function based index will use the UPPER function to force all index entries to upper case, and, by being a unique index, eliminate the possibilities of duplicate entries.
DELETE FROM depts WHERE dept_name = 'Research';

SELECT * FROM depts;

ALTER TABLE depts
DROP CONSTRAINT uc_dept_name;

CREATE UNIQUE INDEX fbi_depts_dept_name
ON depts(UPPER(dept_name));
Lets try it out:
SELECT * FROM depts;

INSERT INTO depts (dept_id, dept_name) VALUES ('4', 'Research');
The unique function based index solves the problem. Even though the data in the table is case insensitive ... the function based index forces all index entries to upper case and generates an exception if a duplicate is entered.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved