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

Home -> Community -> Usenet -> c.d.o.tools -> Re: Primary Key

Re: Primary Key

From: Chris Hamilton <ToneCzar_at_erols.com>
Date: 1999/04/10
Message-ID: <37115154.2656033@news.erols.com>#1/1

On Sat, 10 Apr 1999 01:49:39 -0400, Genna <sliders1_at_mailcity.com> wrote:

>I created a table using
>
>create table my_table
>as select x, y , y
>from d
>
>when I alter the constranint
>
>SQL> alter table my_table
> 2 add (constraint my_table_pk primary key (x));
>alter table my_table
>*
>ERROR at line 1:
>ORA-02437: cannot enable (ORGANIZATION.MY_TABLE_PK) - primary key
>violated>
>
>anyone know how to alter the primary key without without getting a
>violation??

You've got duplicate rows in there. You need to find out which ones.

First, you should create the EXCEPTIONS table to hold the ROWIDs of rows that are in violation of the constraint. Run the utlexcpt.sql script (located in $ORACLE_HOME/rdbms/admin or %ORACE_HOME%\RDBMS80\ADMIN) to create the table.

Then rerun your PK creation statement, adding a clause to direct the violators to the exceptions table. Such as:

alter table my_table
add constraint my_table_pk
primary key (x)
exceptions into exceptions;

This will still return the error, and the constraint will not be created, but in the EXCEPTIONS table you'll have the ROWIDs of the duplicate rows and you can investigate from there.

You can also do this (if your column name is "x"):

select x, count(*)
from my_table
group by x
having count(*) > 1;

That will list all the duplicates as well, and you can deal with them accordingly.

Also, I think Oracle8 has some sort of deferred constraint validation, so maybe you can create the constraint but not have it enforced yet until you track down the culprits.

Chris



Christopher Hamilton
Oracle DBA -- Wall Street Sports
chris_at_wallstreetsports.com
http://www.wallstreetsports.com/ Received on Sat Apr 10 1999 - 00:00:00 CDT

Original text of this message

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