Home » SQL & PL/SQL » SQL & PL/SQL » how to make a column primary key which contains duplicate data (oracle 10g)
how to make a column primary key which contains duplicate data [message #601640] Sat, 23 November 2013 11:14 Go to next message
jgjeetu
Messages: 208
Registered: July 2013
Location: India
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 #601642 is a reply to message #601640] Sat, 23 November 2013 11:28 Go to previous messageGo to next message
Littlefoot
Messages: 19326
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You 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.
Re: how to make a column primary key which contains duplicate data [message #601643 is a reply to message #601642] Sat, 23 November 2013 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 22520
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E16655_01/server.121/e17633/glossary.htm#CNCPT2084

primary key constraint

An integrity constraint that disallows duplicate values and nulls in a column or set of columns.
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1963
Registered: January 2010
Senior Member
Littlefoot wrote on Sat, 23 November 2013 12:28
You 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 #601670 is a reply to message #601649] Sun, 24 November 2013 02:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1858
Registered: May 2013
Location: World Wide on the Web
Senior Member
A bit more explained here
Re: how to make a column primary key which contains duplicate data [message #601674 is a reply to message #601640] Sun, 24 November 2013 03:45 Go to previous messageGo to next message
jgjeetu
Messages: 208
Registered: July 2013
Location: India
Senior Member
Thanks Solomon and Lalit
The concept of novalidate is clear now :-"novalidate", which instructs Oracle to skip checking of the existing data in the table. This is why the duplicate value in the table were tolerated while creating the constraint.
, Read the whole topic which lalit provided but still not getting properly beause don't know much about index (till yet i know we create index for fast searching) that's why, what is the use of making index here , how is it helping ,. Please explain.
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1858
Registered: May 2013
Location: World Wide on the Web
Senior Member
jgjeetu wrote on Sun, 24 November 2013 15:15
what 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1963
Registered: January 2010
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.
Re: how to make a column primary key which contains duplicate data [message #601682 is a reply to message #601640] Sun, 24 November 2013 07:15 Go to previous message
jgjeetu
Messages: 208
Registered: July 2013
Location: India
Senior Member
thanks Smile
Previous Topic: object type
Next Topic: How To question for analytics
Goto Forum:
  


Current Time: Tue Jul 29 09:15:55 CDT 2014

Total time taken to generate the page: 0.13288 seconds