on delete cascade question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: on delete cascade question

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    on delete cascade question

    Hi friends ,

    I have few tables which has constraints like on delete cascade ..

    one of my table script is like this ...

    just looking at this table can we say ..

    which all records will be deleted if i delete record from this table ..

    please let me know if there is any query which would tell .. which all tables would be deleted .. if i delete record from this table ... because other tables are also having similar constarint on delete cascade mapping this column ...

    CREATE TABLE GRP (
    GRP_ID NUMBER NOT NULL,
    VERSION_ID NUMBER NOT NULL,
    GRP_NBR NUMBER NOT NULL,
    GRP_NM VARCHAR2 (72) NOT NULL,
    CHR CHAR (1) NOT NULL,
    REF_VERSION_ID VARCHAR2 (40) NOT NULL,
    REF_GRP_NBR VARCHAR2 (40) NOT NULL,
    GRP_TYP_CD VARCHAR2 (2) NOT NULL,
    GRP_STATUS VARCHAR2 (1) NOT NULL,
    GRP_DEF VARCHAR2 (4000) NOT NULL,
    TALCT NUMBER DEFAULT 0 NOT NULL,
    SPEC_CD VARCHAR2 (8) NOT NULL,
    ID NUMBER NOT NULL,
    CREATEDBY VARCHAR2 (30),
    OUTN NUMBER,
    CDNG_QUEST VARCHAR2 (20),
    CREATE_START_TIME DATE,
    CREATE_END_TIME DATE,
    CONSTRAINT GRP_CK_1
    CHECK (grp_typ_cd IN ('N','H','O','Y','X', 'Q','#','M')),
    CONSTRAINT GRP_PK
    PRIMARY KEY ( GRP_ID )
    USING INDEX
    TABLESPACE INDX PCTFREE 10
    STORAGE ( INITIAL 20736K NEXT 2048K PCTINCREASE 0 ))
    TABLESPACE OLRT NOLOGGING
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 56770560
    NEXT 1474560
    PCTINCREASE 1
    MINEXTENTS 1
    MAXEXTENTS 90
    FREELISTS 1 FREELIST GROUPS 1 )
    NOCACHE;

    ALTER TABLE GRP ADD CONSTRAINT GRP_FK_1
    FOREIGN KEY (GRP_TYP_CD)
    REFERENCES ISRTEST.GRP_TYP (GRP_TYP_CD) ON DELETE CASCADE;

    ALTER TABLE GRP ADD CONSTRAINT GRP_FK_2
    FOREIGN KEY (CHR)
    REFERENCES ISRTEST.HATCH_COLOR (CHR) ON DELETE CASCADE;

    ALTER TABLE GRP ADD CONSTRAINT GRP_FK_3
    FOREIGN KEY (VERSION_ID)
    REFERENCES ISRTEST.VERSION (VERSION_ID) ON DELETE CASCADE;




    CREATE INDEX GRP_VID_CDNG_QUEST_IDX ON
    GRP(VERSION_ID, CDNG_QUEST)
    TABLESPACE INDX PCTFREE 10 STORAGE(INITIAL 14811136 NEXT 7364608 PCTINCREASE 0 )
    ;

    CREATE INDEX XIF368GRP ON
    GRP(VERSION_ID, GRP_NBR)
    TABLESPACE INDX PCTFREE 10 STORAGE(INITIAL 15859712 NEXT 1048576 PCTINCREASE 0 )
    ;

    CREATE INDEX XIF422GRP ON
    GRP(GRP_TYP_CD)
    TABLESPACE INDX PCTFREE 10 STORAGE(INITIAL 11272192 NEXT 516096 PCTINCREASE 0 )
    ;
    Last edited by prakashs43; 09-10-2004 at 12:41 PM.

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    actually this table is referenced by several tables more than 100 tables ...

    this table has 3 table references
    GRP_TYP
    HATCH_COLOR
    VERSION

  3. #3
    Join Date
    Jul 2003
    Posts
    323
    The DELETE_RULE col. of *_constraints should give you this info - just write a script for the tables that you want this info. for !!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width