Re: alter table disable constraint

From: <sunk_at_focushope.edu>
Date: Fri, 09 Feb 2001 14:08:12 GMT
Message-ID: <960tk6$t65$1@nnrp1.deja.com>

In article <3A802540.F4FDA1A1_at_alliedsignal.com>,   Naushi Hussain <naushi.hussain_at_alliedsignal.com> wrote:
> 1) I did an alter table disable primary key cascade and it worked.
 Now
> I like to enable it. Will enabling automatically recreate the FK
> constraint?
>
> 2) Also I tried looking in dba_constraints table for the table this
 key
> is a FK to. But I could not find the related table_name even in the
> R_CONSTRAINT_NAME. How can I find the name of the (child) table where
> the primary key of a (parent) table is a foreign key.
>
> will appreciate any suggestions
>
>

Use this script:

----------------cut------------cut------------------------
rem ****************************************************************

rem
rem If necessary, reformat the columns to avoid column wrapping. rem
rem Change the table name USER_CONSTRAINTS to ALL_CONSTRAINTS or rem DBA_CONSTRAINTS to change the scope of the query. rem
rem To list the reference on a particular table only, add the table rem name to the WHERE clause, i.e.,
rem
rem AND A.TABLE_NAME = &tbl_name
rem

rem ****************************************************************


column table_name         format a20
column key_name           format a14

column referencing_table format a20
column foreign_key_name format a14
column fk_status format a8

set linesize 80
set pagesize 60
set tab off
set space 1

SPOOL fk.log

SELECT

    A.TABLE_NAME table_name,
    A.CONSTRAINT_NAME key_name,
    B.TABLE_NAME referencing_table,
    B.CONSTRAINT_NAME foreign_key_name,
    B.STATUS fk_status

  FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B   WHERE

• A.TABLE_NAME='CONTROL_PLAN' AND A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and B.CONSTRAINT_TYPE = 'R' ORDER BY 1, 2, 3, 4;

SPOOL OFF

----------------cut------------cut------------------------

HTH. Kurt

Sent via Deja.com
http://www.deja.com/ Received on Fri Feb 09 2001 - 08:08:12 CST