Procedure for changing foreign keys

An Oracle package that will create a script to change foreign key options.

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 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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close