Hanging Constraints

I think this may be a bug in Oracle, but Im not 100% sure.

A little background: I have a script which builds up and tears down a database schema for testing purposes. This script is run several times a day, and usually works fine but I just had the following issue arise.

1. In this script, I have a table called CATALOG_ITEM.
2. The script tries to add a primary key to this table via:

ALTER TABLE CATALOG_ITEM ADD ( CONSTRAINT PK_CATALOG_ID PRIMARY KEY (CATALOG_ID) NOT DEFERRABLE INITIALLY IMMEDIATE ) 3. However, this complains that:
"name already used by an existing constraint"

4. When I do a SELECT * FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME =
'PK_CATALOG_ID' there is nothing in there with that name.

5. However, if I do a SELECT * FROM SYS.OBJ$ WHERE NAME =
'PK_CATALOG_ID' there is an entry for an object.

6. If I try and drop this constraint from the table, I recieve: Cannot drop constraint - nonexistent constraint

7/ Alarmingly, this query returns a few rows, one of them being the PK_CATALOG_ID: SELECT * FROM SYS.CDEF$ WHERE OBJ# NOT IN (SELECT OBJ# FROM SYS.OBJ$) I'm assuming (maybe incorrectly) that CDEF$ is short for constraints definition, and that I have constraints that are now pointing to tables that no longer exist.

Any idea how to clean this up or what would cause it? It seems that none of the SYS
tables that I'm looking at have foreign key constraints so I'm extremely hesitant to start deleting or even touching records in the SYS schema.

Thanks,
Tim Orme Received on Wed Apr 23 2008 - 09:42:18 CDT