Home » SQL & PL/SQL » SQL & PL/SQL » Check uniqueness of data.
Check uniqueness of data. [message #347923] Mon, 15 September 2008 01:43 Go to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

I am working on a requirment to prevent duplicate data insertion into table.

For example,


SQL> insert into test1 (NAME) values('ABC123');

1 row created.

SQL> insert into test1 (NAME) values('abc123');

This secon insert should throw an error instead of inserting the value 'abc123' into table TEST1. This is because the same data in different case is present in the table.

Column NAME has a unique constraint on it.

Thanks in advance
Re: Check uniqueness of data. [message #347924 is a reply to message #347923] Mon, 15 September 2008 01:45 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
I know one way is to have a BEFORE insert trigger on the table TEST1.

Let me know other possiblities to perform this check.

Re: Check uniqueness of data. [message #347929 is a reply to message #347923] Mon, 15 September 2008 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a unique index on "lower(name)"

Regards
Michel
Re: Check uniqueness of data. [message #347955 is a reply to message #347929] Mon, 15 September 2008 03:18 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi Michel,

As i already have a unique key on the column name, i am not able to add a unique index on the same column.

create unique index idx_test1_name on test1(lower(name)) tablespace saas_dat
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Let me know how to solve this.
Re: Check uniqueness of data. [message #347957 is a reply to message #347955] Mon, 15 September 2008 03:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That error means that you have already duplicate ENTRIES in the table.

That means two or more values for NAME that are identical when converted to lower case.

You first have to check for those, and delete/change the duplicates.
Re: Check uniqueness of data. [message #347958 is a reply to message #347955] Mon, 15 September 2008 03:29 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh? This error ("ORA-01452: cannot CREATE UNIQUE INDEX;") doesn't mean that there already IS unique constraint, but just the opposite:
Quote:
duplicate keys found

First remove duplicates, then apply unique constraint!

BTW, "abc123" is different from "ABC123" so letter case is not responsible for your problems.

SQL> create table test (col varchar2(20));

Table created.

SQL> alter table test add constraint uk_test unique (col);

Table altered.

SQL> insert into test (col) values ('abc123');

1 row created.

SQL> insert into test (col) values ('ABC123');

1 row created.

SQL> insert into test (col) values ('abc123');
insert into test (col) values ('abc123')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_TEST) violated


SQL>

Perhaps you should pay more attention to both documentation and Oracle error messages you receive when doing the job.
Re: Check uniqueness of data. [message #347961 is a reply to message #347923] Mon, 15 September 2008 03:35 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
I understand what you have explained in your reply.

But my requirement is if the value 'abc123' is present in the Db then, the user must not be able to insert 'ABC123' or 'Abc123' or 'AbC123' or 'abC123' or 'aBc123' or 'aBC123' etc...

I would like to know is there any other way to achieve this other than using a BEFORE INSERR trigger on the table TEST1.

Re: Check uniqueness of data. [message #347963 is a reply to message #347961] Mon, 15 September 2008 03:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You already know another way:

create unique index idx_test1_name 
    on test1(lower(name)) 
       tablespace saas_dat;


But you CAN'T use that way right now, because there already ARE wrong/duplicate entries in the table. So you have to CORRECT them first before you can create the unique index.
Re: Check uniqueness of data. [message #347964 is a reply to message #347961] Mon, 15 September 2008 03:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd like to see exactly how you plan to do this using a trigger, without having it fail utterly in a multi user situation.

If you only want this condiiton to apply to data inserted into the database from this point on, then I woud do this:

1) Create another column in the table, which is populated with LOWER(your_column)
2) Create a unique constraint on this new column with the constraint state set to ENABLE NOVALIDATE
This will check new data matches the unique constraint, but will no tenforce validation on the data that currently exists in the table.

{added more explanation}

[Updated on: Mon, 15 September 2008 03:50]

Report message to a moderator

Re: Check uniqueness of data. [message #347965 is a reply to message #347961] Mon, 15 September 2008 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I understand what you have explained in your reply.
But my requirement is if the value 'abc123' is present in the Db then, the user must not be able to insert 'ABC123' or 'Abc123' or 'AbC123' or 'abC123' or 'aBc123' or 'aBC123' etc...

You didn't understand what has been posted.

1/ Remove duplicates
2/ Create the index I mentioned

Regards
Michel
Re: Check uniqueness of data. [message #347973 is a reply to message #347965] Mon, 15 September 2008 04:11 Go to previous message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Thanks for your reply guys... its working now.

Previous Topic: Multiple where conditions for a column in a single query
Next Topic: Truncate the full database
Goto Forum:
  


Current Time: Thu Dec 08 10:43:02 CST 2016

Total time taken to generate the page: 0.43134 seconds