Re: Cannot drop SYS_ constraints - Why?

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/06/07
Message-ID: <339905F6.2F5B@postoffice.worldnet.att.net>#1/1

First, what do you mean by "...many duplicate SYS_ constraints on many columns." ? How did you reach this conclusion ?

Second, the NOT NULL condition on any table column is enforced by ORACLE through a CHECK constraint (constraint_type='C'), i.e. CHECK (<column_name IS NOT NULL). Usually, no DBA bothers to create a constraint name for a NOT NULL condition, therefore these are named by ORACLE in the standard SYS_Cnnn format. Those are probably what you mistakenly consider duplicate constraints. To verify, join ALL_CONSTRAINTS with ALL_CONS_COLUMNS table to obtain the column names to which the CHECK constraints apply. You could also check the SEARCH_CONDITION column in the ALL_CONSTRAINTS table.

If my assumption is correct, when you attempt to drop a SYS_ constraint which corresponds to a NOT NULL condition, you ask ORACLE to execute the following SQL statement:

ALTER TABLE product MODIFY (<column_name> NULL), which it will not do (looks like ORACLE does not consider these as actual constraints).

As an exercise, make the NOT NULL columns in the PRODUCT table NULL and query the ALL_CONSTRAINTS table to see if the SYS_ constraints are still there.

I hope this helps.

Michael Serbanescu

---

Simon Goland wrote:
>
> For some reason (I guess multiple imports...) we now have many duplicate
> SYS_ constraints on many columns. So I wrote a combination of SQL/perl
> scripts to remove all duplicate SYS_ constraints for all columns in all
> tables. However, for some reason, these cannot be dropped (ORA-02443).
>
> Running the query for a specific user
>
> SELECT constraint_name,constraint_type,table_name
> FROM all_constraints
> WHERE table_name = 'PRODUCT'
> AND constraint_name like 'SYS%';
>
> gives me something like
>
> CONSTRAINT_NAME C TABLE_NAME
> ------------------------------ - ------------------------------
> SYS_C0044054 C PRODUCT
> SYS_C0044055 C PRODUCT
> SYS_C0044056 C PRODUCT
>
> (more output deleted... )
> (all constraints are ENABLED)
>
> And when I run the command (as the very same user)
>
> alter table PRODUCT drop constraint SYS_C0044056;
>
> I keep getting:
>
> alter table product drop constraint SYS_C0044056
> *
> ERROR at line 1:
> ORA-02443: Cannot drop constraint - nonexistent constraint
>
> What am I missing here?
>
> Thanks,
> --
> [ Simon Goland B-)> sg_at_mda.ca ]
> [ Without action there is no change ]
Received on Sat Jun 07 1997 - 00:00:00 CDT