Home » SQL & PL/SQL » SQL & PL/SQL » UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS
UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665512] Fri, 08 September 2017 03:14 Go to next message
Poovaraghavan
Messages: 13
Registered: August 2017
Junior Member
Hi,

Kindly help me on the following concerns, how to add unique index on the existing column with duplicate records.

Please do the needful
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665514 is a reply to message #665512] Fri, 08 September 2017 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Normally you would get rid of the duplicates first.
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665516 is a reply to message #665514] Fri, 08 September 2017 03:48 Go to previous messageGo to next message
Poovaraghavan
Messages: 13
Registered: August 2017
Junior Member
Without rid of, how to achieve this. Please let me know..
There is any possibility ?
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665519 is a reply to message #665516] Fri, 08 September 2017 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sure; ENABLE NOVALIDATE option(s).

SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (2);

1 row created.

SQL> select * From test;

        ID
----------
         1
         1
         2

Now, create a non-unique index (if you don't do that, ALTER TABLE (which follows) will fail because it'll also try to create a unique index on that column):
SQL> create index i1_id on test (id);

Index created.

SQL> alter table test add constraint uk_id unique (id) enable novalidate;

Table altered.

OK; now some more testing:
SQL> insert into test values (2);
insert into test values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (TRAFOGLED.UK_ID) violated


SQL> insert into test values (3);

1 row created.

SQL>
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665520 is a reply to message #665519] Fri, 08 September 2017 04:36 Go to previous messageGo to next message
Poovaraghavan
Messages: 13
Registered: August 2017
Junior Member
Thanks bro.
but i am asking to use the unique index in existing column which contains duplicate records...
I tried "NOVALIDATE", it was not working.
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665521 is a reply to message #665520] Fri, 08 September 2017 04:43 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's nothing I'd like to add to my previous message.
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665523 is a reply to message #665521] Fri, 08 September 2017 05:25 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
As far I know, it is impossible to add a unique INDEX to a column with duplicates. LF has handled the constraint fix above.
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665524 is a reply to message #665523] Fri, 08 September 2017 06:37 Go to previous messageGo to next message
Poovaraghavan
Messages: 13
Registered: August 2017
Junior Member
Thanks Bro,

I found that if we use "deferrable novalidate" in unique constraint syntax. It is creating a unique index for that column. But we cannot able to create the unique index directly for the above scenario...

" ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME UNIQUE(COLUMN_NAME1....) DEFERRABLE NOVALIDATE "
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665525 is a reply to message #665524] Fri, 08 September 2017 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you notice that LF said you had to create a non-unique index first?
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665526 is a reply to message #665525] Fri, 08 September 2017 07:04 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Let me ask this then: Why do you want a unique index? What is it going to give you? The only thing I can think of is that the optimizer will know it is a unique key.
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665560 is a reply to message #665512] Mon, 11 September 2017 04:07 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
and it will also prevent further duplicate inserts on the table
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665564 is a reply to message #665524] Mon, 11 September 2017 05:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Poovaraghavan wrote on Fri, 08 September 2017 12:37
Thanks Bro,

I found that if we use "deferrable novalidate" in unique constraint syntax. It is creating a unique index for that column. But we cannot able to create the unique index directly for the above scenario...

" ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME UNIQUE(COLUMN_NAME1....) DEFERRABLE NOVALIDATE "
No, this syntax does not create a unique index. It creates a nonunique index. You'll see this if you check dba_indexes.uniqueness
Re: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS [message #665566 is a reply to message #665560] Mon, 11 September 2017 05:42 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
OraFerro wrote on Mon, 11 September 2017 10:07
and it will also prevent further duplicate inserts on the table
So would a non-unique index utilising the constraint Smile
Previous Topic: left outer join of 3 tables
Next Topic: Finding Date overlap
Goto Forum:
  


Current Time: Fri Mar 29 06:34:58 CDT 2024