| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Primary Key
On Sat, 10 Apr 1999 15:31:12 GMT, "Mike Rose" <mmrose_at_home.com> wrote:
>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.
Yes, it is dangerous. I hadn't read up on what exactly it was all about, just heard that it existed. Thanks for clarifying that.
>
>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/
 |
 |