how to make a column primary key which contains duplicate data [message #601640] |
Sat, 23 November 2013 11:14 |
|
jgjeetu
Messages: 373 Registered: July 2013 Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
I have table abc with two columns 1. id number 2. name varchar2(5).
abc
id name
1 a
1 a
2 b
2 b
2 b
3 c
4 d
Now my requirement is i want to make id column as primary key
but the condition is i don't want to delete duplicate data from column
now please tell me how to achieve this target.
if possible then please provide step by step query with explanation.
Thanks
[Updated on: Sat, 23 November 2013 11:15] Report message to a moderator
|
|
|
|
|
Re: how to make a column primary key which contains duplicate data [message #601649 is a reply to message #601642] |
Sat, 23 November 2013 12:37 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Littlefoot wrote on Sat, 23 November 2013 12:28You can't do that, at least not with data you posted (not ID itself, not ID + NAME). Either get rid of duplicates, or alter table and add a new column which will be used as a primary key.
Well, it depends. Oracle allows to preserve existing duplicates and make future inserts/updates to honor PK. If that's what OP wants:
SQL> create table tbl
2 as
3 select 1 id,'a' name from dual union all
4 select 1,'a' from dual union all
5 select 2,'b' from dual union all
6 select 2,'b' from dual union all
7 select 2,'b' from dual union all
8 select 3,'c' from dual union all
9 select 4,'d' from dual
10 /
Table created.
SQL> alter table tbl
2 add constraint tbl_pk
3 primary key(id)
4 /
add constraint tbl_pk
*
ERROR at line 2:
ORA-02437: cannot validate (SCOTT.TBL_PK) - primary key violated
SQL> create index tbl_pk
2 on tbl(id)
3 /
Index created.
SQL> alter table tbl
2 add constraint tbl_pk
3 primary key(id)
4 using index tbl_pk
5 /
add constraint tbl_pk
*
ERROR at line 2:
ORA-02437: cannot validate (SCOTT.TBL_PK) - primary key violated
SQL> alter table tbl
2 add constraint tbl_pk
3 primary key(id)
4 using index tbl_pk
5 novalidate
6 /
Table altered.
SQL> insert
2 into tbl
3 values(5,'e')
4 /
1 row created.
SQL> /
insert
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TBL_PK) violated
SQL>
SY.
|
|
|
|
|
Re: how to make a column primary key which contains duplicate data [message #601676 is a reply to message #601674] |
Sun, 24 November 2013 05:52 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
jgjeetu wrote on Sun, 24 November 2013 15:15what is the use of making index here , how is it helping
The demonstration is self explanatory. When we create a primary key or a unique constraint, an unique index is enforced.
However, in this case, to allow the existing duplicates in the table, we are creating a primary key and instructing Oracle not to let the constraint to validate the data integrity while using the pre-created index. Now, the pre-created index is not unique, it's a regular index, and that's pretty much OK to suffice our requirements(since we do not need any unique index at all).
|
|
|
Re: how to make a column primary key which contains duplicate data [message #601677 is a reply to message #601674] |
Sun, 24 November 2013 05:57 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Index is internal mechanism Oracle uses to support PK. With unique index PK support is easy (we not't need to lift a finger - index will fully support PK) but infelxible - we can't disable PK, we can't use NOVALIDATE (having UNIQUE index will prevent it). In order to maintain these options we need to relax index restrictions. Uniqueness, to be precise. That's why Oracle allows creating PK uning non-unique index.
SY.
|
|
|
|