From oracle-l-bounce@freelists.org Fri Oct 21 02:18:53 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9L7IqsQ011338 for ; Fri, 21 Oct 2005 02:18:53 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9L7IevX011294 for ; Fri, 21 Oct 2005 02:18:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CC20E20A95C; Fri, 21 Oct 2005 01:22:17 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10187-08; Fri, 21 Oct 2005 01:22:17 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4BD2C20A959; Fri, 21 Oct 2005 01:22:17 -0500 (EST) Date: Fri, 21 Oct 2005 06:20:17 +0000 From: Mladen Gogala Subject: Re: ** anyway to create a primary key on table with duplicates that cannot be deleted To: anysql@gmail.com Cc: ajoshi977@yahoo.com, oracle-l@freelists.org References: <20051019175811.44385.qmail@web60712.mail.yahoo.com> In-Reply-To: (from anysql@gmail.com on Fri Oct 21 01:19:10 2005) Message-Id: <1129875617l.5415l.2l@medo.noip.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis X-archive-position: 27318 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: gogala@sbcglobal.net Precedence: normal Reply-To: gogala@sbcglobal.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.0 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 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