Re: Can I add primary key constraint?

In article <ONy85.235$1b1.97087_at_nnrp3.sbc.net>,   "spencer" <spencerp_at_swbell.net> wrote:
> "Carfield Yim" <carfield_at_my-deja.com> wrote in message
> news:8juad8$edg$1_at_nnrp1.deja.com...
> > When I use the following SQL command to add primary key
> > constraint:
> >
> > alter table deletelog add constraint pk_deletelog primary key
> > (type, id) using index tablespace renren_idx
> >
> > I receive the following error message:
> >
> > SQL> alter table deletelog add constraint pk_deletelog primary
> > key(type, id);
> > alter table deletelog add constraint pk_deletelog primary key
> > (type, id) using index
> > *
> > ERROR at line 1:
> > ORA-02437: cannot enable (RENREN.PK_DELETELOG) - primary key
> > violated
> >
> > How can I add primary key constraint?
> >
>
> most likely, there are one or more rows where one of the
> two columns contains a NULL, or where the combination
> of the two columns is not unique.
>
> to find the rows that are "violating" the constraint you are
> trying to add, you could run some SQL statements, e.g.
>
> select rowid, *
> from <table>
> where <column1> is null
> or <column2> is null ;
>
> select <column1>, <column2>
> from <table>
> group by <column1>,<column2>
> having count(*) > 1 ;
>
> alternatively, you can create an EXCEPTIONS table, and
> modify your original 'alter table' statement to have it put
> the problem rows into the EXCEPTIONS table for you.
>
> either way, you need to identify the problem rows (ie. the
> rows that are preventing the constraint from being enabled.
> and fix them by either updating or deleting them from the
> table, and try enabling the constraint again.
>
> another alternative is to add the constraint "disabled", by
> including the DISABLE keyword, although you still won't
> be able to enable the constraint until you've fixed the rows
> that violate the constraint.
>
> HTH
>
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>

Create the EXCEPTIONS table with the
$ORACLE_HOME/rdbms/admin/utlexcpt.sql script. Then modify your statement in this manner:

 alter table deletelog add constraint pk_deletelog primary key  (type, id) using index tablespace renren_idx  exceptions into exceptions

The primary key still won't validate but you'll know which rows are causing the problem.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.