Re: Primary Key

That's very dangerous thinking. The No Validate Mode is intended to save the unnecessary checking when a constraint is enabled. The assumption is that you already have valid data in the index column(s) when you enable the constraint(s). It is also multi-threaded to you can enable many constraints via scripting.

Mike Rose

Chris Hamilton <ToneCzar_at_erols.com> wrote in message news:37115154.2656033_at_news.erols.com...
> 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