Re: Can I add primary key constraint?

"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.
>
Received on Tue Jul 04 2000 - 00:00:00 CDT