Re: Dropping constraints ??

From: Jozef Starosta <josef_at_mars.softouch.bc.ca>
Date: 1995/07/12
Message-ID: <3u0qu4$jmp_at_mars.softouch.bc.ca>#1/1

huckleberry (jalford_at_immcms1.redstone.army.mil) wrote:

: In my haste to create some tables, I put a REFERENCES constraint on a field
: and did not give the constraint a name, now I need to change the data.
: I have tried unsuccessly to disable this nameless constraint.
: Is there no way
: to disable this constraint ??
 

: constraint:
 

: loan_number varchar2(12)
: REFERENCES LOAN(loan_number)
: ON DELETE CASCADE,
try this:

SQL> connect jojo/jojo
Connected.
SQL> create table loan(loan_number varchar2(12),

  2                    unique (loan_number));

Table created.

SQL> create table loan_owner

  2     (owner_name varchar2(32),
  3      loan_number varchar2(12)
  4           REFERENCES LOAN(loan_number) ON DELETE CASCADE);

Table created.

SQL> connect system/manager
Connected.
SQL> select * from dba_constraints where owner = 'JOJO';

                       Softouch Information System Report

OWNER                          CONSTRAINT_NAME                C                 
------------------------------ ------------------------------ -                 
TABLE_NAME                                                                      
------------------------------                                                  
SEARCH_CONDITION                                                                
------------------------------------------------------------
R_OWNER           R_CONSTRAINT_NAME        DELETE_RU STATUS  
----------------- --------------------------------- --------
JOJO              SYS_C002786              U                 
LOAN                                                                            
                                                                                
                                                     ENABLED 
                                                                                
JOJO              SYS_C002787              R                 
LOAN_OWNER                                                                      
                                                                                
JOJO              SYS_C002786              CASCADE   ENABLED 
                                                                                

SQL> connect jojo/jojo
Connected.

SQL> alter table loan drop constraint SYS_C002786; alter table loan drop constraint SYS_C002786

                                 *

ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

SQL> alter table loan_owner drop constraint SYS_C002787;

Table altered.

SQL> exit

Hope it helps

Jozef Received on Wed Jul 12 1995 - 00:00:00 CEST