-
I have a table where four fields will together form the primary key. However, the data thats already in there will violate the primary key, so I want to create it novalidate so the existing data can stay, yet any new data entered will be checked. However, I can't get novalidate to work. I made a copy of the table, deleted all the records from the original, created the constraint, disabled the constraint, copied the data from the temp table back into the original, and up to there everything is fine. Then, when I issue:
alter table matdata enable novalidate constraint matdata_pk
I get:
SQL> alter table matdata enable novalidate constraint matdata_pk;
alter table matdata enable novalidate constraint matdata_pk
*
ERROR at line 1:
ORA-02437: cannot validate (TABS.MATDATA_PK) - primary key violated
So, how is this even possible? What am I not getting? Oh, btw, I am using Oracle8i on Solaris 8.
Thanks,
D-J
-
Did you create the PK with deferrable option?
I know its not required for 'DISABLE/ ENABLE CONSTRAINT feature but in my opinion, the problem might be due to Unique index created by Oracle when you created the PK. If the constraint is created with deferable option, the associated index is non-unique and can store PK violators.
-
Check for any Unique index on any of the columns forming your PK with the following statement:
Code:
select c.column_name, i.uniqueness
from
user_ind_columns c, user_indexes i
where
c.table_name='METADATA'
and
c.Index_name=i.index_name
/
if there is some unique index, you will have to drop it before enabling constraint.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|