Tip

Procedure for changing foreign keys

This package will help you create a script to change foreign key options in Oracle. Here goes:

                
CREATE OR REPLACE 
PACKAGE manager_db 
IS 

   -- Gerasimov Sergey ehcap@mail.ru 
   -- Purpose: Contains expansions for ORACLE 
   -- TYPE name_object IS TABLE OF user_tables.table_name%TYPE 
   -- INDEX BY BINARY_INTEGER; 
   -- Worked_table is used for optimization of viewing dependent tables 
      worked_table   name_object; 
   
   -- This procedure is used for clearing 
   -- before start of procedure CHANGE_FOREIGN_KEY 
   procedure clear_worked_table; 

   -- Creation of a script for change FK, referring on the table NAME_TABLE 
   -- Must run in SQLPLUS. 
   -- Limitations: This function works in current scheme. 
   -- NAME_TABLE   - name of the table to which refer FK (mast be UPPER) 
   -- NEW_OPTIONS  - string containing new options for creation FK 
   --                (ex. 'ON DELETE SET NULL DEFERRABLE') 
   -- RECURSIVE    - 1(default) - create script for all dependent tables recursively 
   --              - 0 - create script for dependent tables non recursively. 
   -- Example 
   -- SQL> set serverout on size 1000000; 
   -- SQL> spool c:fk.lst 
   -- SQL> exec manager_db.clear_worked_table; 
   -- 
   -- PL/SQL procedure successfully completed. 
   -- 
   -- SQL> exec manager_db.change_foreign_key('OBJ','ON DELETE SET NULL DEFERRABLE'); 
   -- -- Create for OBJ 
   -- ALTER TABLE ADDRESS DROP CONSTRAINT

    Requires Free Membership to View

FK_ADDRESS_OBJ; -- ALTER TABLE ADDRESS -- ADD CONSTRAINT FK_ADDRESS_OBJ FOREIGN KEY (OBJ_ID) -- REFERENCES OBJ(OBJ_ID) -- ON DELETE SET NULL DEFERRABLE; -- -- Create for ZONE_SPEC_USE -- ALTER TABLE ZONE_SPEC_USE DROP CONSTRAINT FK_ZONE_SPEC_USE_ZONE_SPEC_US; -- ALTER TABLE ZONE_SPEC_USE -- ADD CONSTRAINT FK_ZONE_SPEC_USE_ZONE_SPEC_US FOREIGN KEY (ZON_ZSU_UNID) -- REFERENCES ZONE_SPEC_USE(ZSU_UNID) -- ON DELETE SET NULL DEFERRABLE; -- -- --PL/SQL procedure successfully completed. PROCEDURE change_foreign_key ( name_table IN VARCHAR2, new_options IN VARCHAR2, recursive IN NUMBER DEFAULT 1 ); END; / CREATE OR REPLACE PACKAGE BODY manager_db IS -- PROCEDURE change_foreign_key ( name_table IN VARCHAR2, new_options IN VARCHAR2, recursive IN NUMBER DEFAULT 1 ) IS CURSOR list_fk (parent IN VARCHAR2) IS SELECT t.table_name, t.constraint_name, con.table_name AS detail, con.constraint_name AS for_rebuild FROM user_constraints t, user_constraints con WHERE t.table_name = parent AND t.constraint_type IN ('P', 'U') AND t.constraint_name = con.r_constraint_name ORDER BY detail, for_rebuild; CURSOR list_fk_t (parent IN VARCHAR2) IS SELECT DISTINCT t.table_name, t.constraint_name, con.table_name AS detail FROM user_constraints t, user_constraints con WHERE t.table_name = parent AND t.constraint_type IN ('P', 'U') AND t.constraint_name = con.r_constraint_name AND con.table_name <> parent ORDER BY detail; CURSOR list_columns (parent_constr IN VARCHAR2) IS SELECT c.column_name, c.position FROM user_cons_columns c WHERE c.constraint_name = parent_constr ORDER BY c.position; out_put_string VARCHAR2(2000); pk_columns VARCHAR2(2000); fk_columns VARCHAR2(2000); tab_det NUMBER := 0; j NUMBER; BEGIN null; if worked_table.count >0 then FOR j IN worked_table.FIRST .. worked_table.LAST LOOP IF worked_table (j) = name_table THEN tab_det := 1; END IF; END LOOP; end if; IF tab_det = 0 THEN j := worked_table.COUNT + 1; worked_table (j) := name_table; DBMS_OUTPUT.put_line ('-- Create for ' || name_table); FOR list_fk_rec IN list_fk (name_table) LOOP out_put_string := 'ALTER TABLE ' || list_fk_rec.detail || ' DROP CONSTRAINT ' || list_fk_rec.for_rebuild || ';'; DBMS_OUTPUT.put_line (out_put_string); pk_columns := '!'; FOR list_col_rec IN list_columns (list_fk_rec.constraint_name) LOOP IF pk_columns = '!' THEN pk_columns := list_col_rec.column_name; ELSE pk_columns := pk_columns || ',' || list_col_rec.column_name; END IF; END LOOP; fk_columns := '!'; FOR list_col_rec IN list_columns (list_fk_rec.for_rebuild) LOOP IF fk_columns = '!' THEN fk_columns := list_col_rec.column_name; ELSE fk_columns := fk_columns || ',' || list_col_rec.column_name; END IF; END LOOP; out_put_string := 'ALTER TABLE ' || list_fk_rec.detail; DBMS_OUTPUT.put_line (out_put_string); out_put_string := ' ADD CONSTRAINT ' || list_fk_rec.for_rebuild || ' FOREIGN KEY (' || fk_columns || ')'; DBMS_OUTPUT.put_line (out_put_string); out_put_string := ' REFERENCES ' || name_table || '(' || pk_columns || ')'; DBMS_OUTPUT.put_line (out_put_string); out_put_string := ' ' || new_options || ';'; DBMS_OUTPUT.put_line (out_put_string); DBMS_OUTPUT.put_line ('--'); END LOOP; IF recursive = 1 THEN FOR list_fk_t_rec IN list_fk_t (name_table) LOOP manager_db.change_foreign_key (list_fk_t_rec.detail, new_options); END LOOP; END IF; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Errors. '); END change_foreign_key; procedure clear_worked_table is begin worked_table.delete; end; END; -- Package Body /

Reader feedback

Peter R. writes: "Well, I thought this looked useful. Then I looked at the text. Minimal documentation. To work out exactly what script does, one would have to work through it. Not good enough! Need to have objectives clearly documented, and THEN read through the code to validate the objectives, not to work them out. Bags of code. But WHY does one have to insert table name in upper case? And as spelling wrong ('mast be UPPER') was so obvious, confidence in actually running the script evaporated. I've re-written the script with proper documentation here.

For More Information

  • What do you think about this tip? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in November 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.