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