Home » SQL & PL/SQL » SQL & PL/SQL » how do i use the dictionary to know there is on delete cascade??
how do i use the dictionary to know there is on delete cascade?? [message #201531] Sun, 05 November 2006 12:10 Go to next message
prince973
Messages: 37
Registered: November 2006
Member
hi all
how can i use the dictionary to know whether if i delete a value for parent column would the foreign key be null or would be deleted too
i want to use the dictionary to know if the foreign key had been made with the use of on delete cascade, on delete set null
pls advise...
Re: how do i use the dictionary to know there is on delete cascade?? [message #201539 is a reply to message #201531] Sun, 05 November 2006 13:43 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Just used "audit" option for

see documentation.
http://otn.oracle.com


Hope this helps.
Mohammad Taj.

PS. sorry for otn address i have no direct link for doc.
Re: how do i use the dictionary to know there is on delete cascade?? [message #201541 is a reply to message #201531] Sun, 05 November 2006 16:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Please see the demonstration below that selects the delete_rule column from the user_constraints table after joining to other apropriate tables, so that the only value that you need to provide is the name of the parent table.


-- tables, keys, and data:
SCOTT@10gXE> CREATE TABLE parent_table
  2    (parent_column  NUMBER,
  3  	CONSTRAINT     parent_table_pk
  4  		       PRIMARY KEY (parent_column))
  5  /

Table created.

SCOTT@10gXE> INSERT INTO parent_table VALUES (1)
  2  /

1 row created.

SCOTT@10gXE> INSERT INTO parent_table VALUES (2)
  2  /

1 row created.

SCOTT@10gXE> CREATE TABLE child_table1
  2    (child1_id      NUMBER,
  3  	child_column1  NUMBER,
  4  	CONSTRAINT     child_table_fk1
  5  		       FOREIGN KEY (child_column1)
  6  		       REFERENCES parent_table (parent_column)
  7  		       ON DELETE CASCADE)
  8  /

Table created.

SCOTT@10gXE> INSERT INTO child_table1 VALUES (1, 1)
  2  /

1 row created.

SCOTT@10gXE> CREATE TABLE child_table2
  2    (child2_id      NUMBER,
  3  	child_column2  NUMBER,
  4  	CONSTRAINT     child_table_fk2
  5  		       FOREIGN KEY (child_column2)
  6  		       REFERENCES parent_table (parent_column)
  7  		       ON DELETE SET NULL)
  8  /

Table created.

SCOTT@10gXE> INSERT INTO child_table2 VALUES (2, 2)
  2  /

1 row created.


-- query that shows whether delete_rule is cascade or null:
SCOTT@10gXE> COLUMN parent_tab	FORMAT A15
SCOTT@10gXE> COLUMN parent_key	FORMAT A15
SCOTT@10gXE> COLUMN parent_col	FORMAT A15
SCOTT@10gXE> COLUMN child_tab	FORMAT A15
SCOTT@10gXE> COLUMN foreign_key FORMAT A15
SCOTT@10gXE> COLUMN rule	FORMAT A15
SCOTT@10gXE> COLUMN child_col	FORMAT A15
SCOTT@10gXE> SELECT uc1.table_name	AS parent_tab,
  2  	    uc1.constraint_name AS parent_key,
  3  	    ucc1.column_name	AS parent_col,
  4  	    uc2.table_name	AS child_tab,
  5  	    uc2.constraint_name AS foreign_key,
  6  	    uc2.delete_rule	AS rule,
  7  	    ucc2.column_name	AS child_col
  8  FROM   user_constraints uc1,   user_constraints uc2,
  9  	    user_cons_columns ucc1, user_cons_columns ucc2
 10  WHERE  uc1.table_name	= 'PARENT_TABLE'
 11  AND    uc1.constraint_name = uc2.r_constraint_name
 12  AND    uc1.constraint_name = ucc1.constraint_name
 13  AND    uc2.constraint_name = ucc2.constraint_name
 14  /

PARENT_TAB      PARENT_KEY      PARENT_COL      CHILD_TAB       FOREIGN_KEY     RULE            CHILD_COL
--------------- --------------- --------------- --------------- --------------- --------------- ---------------
PARENT_TABLE    PARENT_TABLE_PK PARENT_COLUMN   CHILD_TABLE1    CHILD_TABLE_FK1 CASCADE         CHILD_COLUMN1
PARENT_TABLE    PARENT_TABLE_PK PARENT_COLUMN   CHILD_TABLE2    CHILD_TABLE_FK2 SET NULL        CHILD_COLUMN2


-- shows that one is deleted and other is null:
SCOTT@10gXE> DELETE FROM parent_table
  2  /

2 rows deleted.

SCOTT@10gXE> SELECT * FROM child_table1
  2  /

no rows selected

SCOTT@10gXE> SELECT * FROM child_table2
  2  /

 CHILD2_ID CHILD_COLUMN2
---------- -------------
         2

SCOTT@10gXE> 


Re: how do i use the dictionary to know there is on delete cascade?? [message #201548 is a reply to message #201531] Sun, 05 November 2006 17:41 Go to previous message
prince973
Messages: 37
Registered: November 2006
Member
Thank you Barbara
great example , appreciated.
Best regards.
Previous Topic: Inline Views
Next Topic: How to find (full) Cost of a query?
Goto Forum:
  


Current Time: Sat Dec 03 00:55:53 CST 2016

Total time taken to generate the page: 0.10439 seconds