Re: ** anyway to create a primary key on table with duplicates that cannot be deleted
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: anysql@xxxxxxxxx
- Date: Fri, 21 Oct 2005 06:20:17 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
