Re: Simple Constraint Question?

From: <markp7832_at_my-deja.com>
Date: 2000/03/12
Message-ID: <8agofe$bkf$1@nnrp1.deja.com>#1/1

In article <8adolt$csd$1_at_nnrp1.deja.com>,   liam_2e_at_my-deja.com wrote:
> Hello Everyone
>
> I am currently working on a table, and I would like to drop a column
> from the table. This is what the table looks like. I would like to
 drop
> the region_no_foreign column except it has a child key. Here take a
> look. How to I find out what column in what table is refering to this
> column so I can remove the constraint??
> Thanks for the help Liam
>
> SQL> delete from state region_no_foreign;
> delete from state region_no_foreign
> *
> ERROR at line 1:
> ORA-02292: integrity constraint (SYSTEM.SYS_C001880) violated - child
> record found
>
> SQL> desc state
> Name Null? Type
> ----------------------------------------------------- --------

---

> STATE_CD NOT NULL
 VARCHAR2
> (2)
> STATE_NM NOT NULL
 VARCHAR2
> (30)
> REGION_NO NUMBER
> (28)
> REGION_NO_FOREIGN NUMBER
> (28)
>
>

The following script might help you; you could also query against all_cons_columns for the column name in question.

set echo off
rem
rem SQL*Plus script to locate foreign keys for a specific table rem
rem 1995 02 05 m d powell New script rem 1998 06 23 m d powell New version using join rem
rem remove 'table_name like' line and all constraints show up rem
set verify off

column FKN               format a30  heading "Foreign Key Name"
column FKT               format a30  heading "Referencing Table"
column RT                format a30  heading "Referenced Table"
column RCN               format a30  heading "Referenced Constraint"
column Status            fold_after

select
       a.constraint_name   "FKN"     ,
       a.table_name        "FKT"     ,
       a.delete_rule       "Rule"    ,
       a.status            "Status"  ,
       b.table_name        "RT"      ,
       a.r_constraint_name "RCN"

from sys.dba_constraints a , sys.dba_constraints b where a.r_constraint_name = b.constraint_name order by a.constraint_name
/
undefine tbl_nm

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sun Mar 12 2000 - 00:00:00 CST