Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** anyway to create a primary key on table with duplicates that cannot be deleted

Re: ** anyway to create a primary key on table with duplicates that cannot be deleted

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 21 Oct 2005 06:20:17 +0000
Message-Id: <1129875617l.5415l.2l@medo.noip.com>

On 10/21/2005 01:19:10 AM, Lou Fangxin wrote:
> Hi all:
> Create a non-unique index, then add the primary key constraint with
> "enable novalidate" option, it will keep the new rows inserted unique, while
> keep the current duplicate rows exist in table.

This is an incomplete and unclear reply to the question that is answered in Oracle Concepts manual. This is exactly an example of the clutter that I was talking about in my RTFM post. What is unclear to me is why didn't the OP simply read the answer in the concepts manual and why did you undertake the answering effort which resulted in a completely unclear and useless reply? You should either have invested a little bit more effort or not invested an effort at all. Now, to be true to the form, here is the answer:

SQL> create table emp1 as select * from emp;

Table created.

SQL> create index emp1_empno on emp1(empno) tablespace indx;

Index created.

Note that the index is not unique.

SQL> alter table emp1 add constraint emp1_pk   2 primary key(empno) using index emp1_empno disable;

Table altered.

Constraint is first added in the disabled state.

SQL> alter table emp1 enable novalidate constraint emp1_pk;

Table altered.

Now, we have a table with the working primary key. That was the old 8i way, which required 2 steps. It is possible to do it in a single step, like this:

SQL> alter table emp1 drop constraint emp1_pk;

Table altered.

SQL> alter table emp1 add constraint emp1_pk   2 primary key(empno) using index emp1_empno enable novalidate;

Table altered.

SQL> drop table emp1;

Table dropped.

As this is a 10g database, the following step is useful:

SQL> purge recyclebin;

Recyclebin purged.

SQL> Now, this is a valid reply, with a valid reproach. If you decide to answer someone's question, please make sure that the answer does actually help and doesn't just waste bandwidth.

-- 
Mladen Gogala
http://www.mgogala.com


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 21 2005 - 02:18:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US