Re: Hanging Constraints

>Can you check using the dba_constraints, instead of all_constraints
>and let us know the results:
> SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME =
>'PK_CATALOG_ID'
The constraint is not listed here either. There are no results.

On Apr 23, 12:17 pm, Vince <vinn..._at_yahoo.com> wrote:
> > 6. If I try and drop this constraint from the table, I recieve:
> > Cannot drop constraint - nonexistent constraint
>
> Likely, this is an index of the same name. Where in your script are
> you dropping the PK index?

I had thought of this before, but this doesn't seem to be the case. I looked in the ALL_INDEXES view as well and couldn't find anything.

The script does a simple drop on each table in the schema.

drop table TABLE_NAME cascade constraints

Also, I can run the script repeatedly without problems in other schemas, but when I try and run it in this specific schema, it breaks. I'm assuming that somehow something didn't get dropped properly at one point due to some error, and now I just have bad data hanging around.

Again, if I run this: SELECT * FROM SYS.CDEF$ WHERE OBJ# NOT IN (SELECT OBJ# FROM SYS.OBJ$) some rows are listed, and one of the is the constraint that is causing me problems.

My assumption is that:
The CDEF$ CON# column corresponds to an entry in the constraints table, and the OBJ# column points at the table to which the constraint is applied.

For the above query, the problem constraint lists: CON# OBJ#
 6550 54672

SELECT * FROM CON$ WHERE CON# = 6550 returns a row with the name PK_CATALOG_ID SELECT * FROM OBJ$ WHERE OBJ# = 54672 returns nothing.

-Tim Received on Wed Apr 23 2008 - 12:16:37 CDT