Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Foreign Key constraint Script

Re: Foreign Key constraint Script

From: Danut Bancea <danut_at_telepages.ca>
Date: Mon, 17 Jul 2000 12:25:03 -0400
Message-Id: <10561.112186@fatcity.com> 





This is a multi-part message in MIME format.



------=_NextPart_000_01E5_01BFEFEA.086BC710
Content-Type: text/plain;


        charset="iso-8859-1"


Content-Transfer-Encoding: 7bit



Hi,



I attached this script.


There are two script one which generate a script file to recreate the FK
(fk_table) and other which generate a script file to delete the FK
(fk_table_del).



You need to enter two variable: first the file name and second table name.



The scripts are using utl_file package. If you are on version 8 of the
database you need to set utl_dir parameter and also changed the script with
the new value ( variable v_dir)



Danut,






Danut Bancea


Senior Oracle DBA



Tele-Pages Inc.


Phone : (416) 296-9011 Ext. 2271


Fax : (905) 477-7062


E-mail : danut_at_telepages.ca







> Hi All,

>

> Does any one have any script that will tell me

> a Table has been referenced (foreign key) in how many

> other tables.  I had a tough time droping a table

> since

> it was refered by so many other table.

>

> I tried looking user_constraints but it doesn't give

> you information about primary table.

>

> TIA.


>

>

>

>

>

> =====

> Ashish

> Toronto, Canada

>

> __________________________________________________

> Do You Yahoo!?

> Get Yahoo! Mail - Free email you can access from anywhere!

> http://mail.yahoo.com/

> --

> Author: Ashish Shah

>   INET: ar_shah_at_yahoo.com

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

>




------=_NextPart_000_01E5_01BFEFEA.086BC710
Content-Type: application/octet-stream;


        name="fk_table_del.sql"


Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;


        filename="fk_table_del.sql"






DECLARE



   v_fA        UTL_FILE.FILE_TYPE;
   v_dir       VARCHAR2(100) :=3D '/data3/oradata/utl';
	v_fileA     VARCHAR2(20)  :=3D &b;
   v_line      VARCHAR2(2600):=3D '';
	specific_table_name user_constraints.table_name%TYPE :=3D &specific;
   v_separator VARCHAR2(6) :=3D chr(10);
   v_count     NUMBER(6) :=3D 1;
   v_max_col   NUMBER(3) :=3D 1;=20




   v_constraint_name user_cons_columns.constraint_name%TYPE;
   v_ext_size  VARCHAR2(6) :=3D '128';


--   v_rec_ user_constraints%ROWTYPE;

	v_reference_name  user_cons_columns.constraint_name%TYPE;
	v_reference_table_name user_cons_columns.table_name%TYPE; -- reference =


table name



   CURSOR cr_fk IS  =20

      SELECT *
      FROM user_constraints
      WHERE constraint_type=3D'R' AND
		constraint_name IN=20
		(SELECT constraint_name FROM user_constraints WHERE r_constraint_name =
IN
		(SELECT constraint_name FROM user_constraints WHERE table_name =3D =
specific_table_name))
      order by table_name;

	CURSOR cr_ref_table_name IS
		SELECT table_name
      FROM user_cons_columns
		WHERE constraint_name=3Dv_reference_name;






BEGIN


   v_fA :=3D UTL_FILE.FOPEN(v_dir, v_fileA, 'w');



   FOR v_rec_fk IN cr_fk LOOP


      v_reference_name :=3D v_rec_fk.r_constraint_name;
      OPEN cr_ref_table_name;
      FETCH cr_ref_table_name INTO v_reference_table_name;
      CLOSE cr_ref_table_name;

      v_line :=3D 'ALTER TABLE '||
                 v_rec_fk.table_name||
	             ' DROP CONSTRAINT '||
                v_rec_fk.constraint_name||
                    ' ;';

      UTL_FILE.PUT_LINE(v_fA, v_line);





   END LOOP;


   UTL_FILE.FCLOSE(v_fA);



EXCEPTION=20



WHEN OTHERS THEN



   DBMS_OUTPUT.PUT_LINE(SQLCODE||'  ---   '||SQLERRM);

END;



/




------=_NextPart_000_01E5_01BFEFEA.086BC710
Content-Type: application/octet-stream;


        name="fk_table.sql"


Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;


        filename="fk_table.sql"






DECLARE



   v_fA        UTL_FILE.FILE_TYPE;
   v_dir       VARCHAR2(100) :=3D '/data3/oradata/utl';
	v_fileA     VARCHAR2(20)  :=3D &b;
   v_line      VARCHAR2(2600):=3D '';
	specific_table_name user_constraints.table_name%TYPE :=3D &specific;
   v_separator VARCHAR2(6) :=3D chr(10);
   v_count     NUMBER(6) :=3D 1;
   v_max_col   NUMBER(3) :=3D 1;=20




   v_constraint_name user_cons_columns.constraint_name%TYPE;
   v_ext_size  VARCHAR2(6) :=3D '128';


--   v_rec_ user_constraints%ROWTYPE;

	v_reference_name  user_cons_columns.constraint_name%TYPE;
	v_reference_table_name user_cons_columns.table_name%TYPE; -- reference =


table name



   CURSOR cr_fk IS  =20

      SELECT *
      FROM user_constraints
      WHERE constraint_type=3D'R' AND
		constraint_name IN=20
		(SELECT constraint_name FROM user_constraints WHERE r_constraint_name =
IN
		(SELECT constraint_name FROM user_constraints WHERE table_name =3D =
specific_table_name))
      order by table_name;

   CURSOR cr_column IS
	SELECT * from user_cons_columns
	WHERE constraint_name=3Dv_constraint_name
	ORDER BY position;

	-- find the max num of columns in fk=20
   CURSOR cr_max IS
	SELECT count(*)
        FROM user_cons_columns
	WHERE constraint_name=3Dv_constraint_name;

-- find the reference table name
	CURSOR cr_ref_table_name IS
	SELECT table_name
        FROM user_cons_columns
	WHERE constraint_name=3Dv_reference_name;







BEGIN


   v_fA :=3D UTL_FILE.FOPEN(v_dir, v_fileA, 'w');



   FOR v_rec_fk IN cr_fk LOOP


      v_reference_name :=3D v_rec_fk.r_constraint_name;
      OPEN cr_ref_table_name;
      FETCH cr_ref_table_name INTO v_reference_table_name;
      CLOSE cr_ref_table_name;

      IF v_rec_fk.constraint_name like 'SYS_%' THEN
         v_line :=3D 'ALTER TABLE '||
                   v_rec_fk.table_name||
	           ' ADD CONSTRAINT '||
		   v_rec_fk.table_name||
                   '_'||
                   v_reference_table_name||
		   ' FOREIGN KEY ( ';
      ELSE
          v_line :=3D 'ALTER TABLE '||
                    v_rec_fk.table_name||
	            ' ADD CONSTRAINT '||
                    v_rec_fk.constraint_name||
                    ' FOREIGN KEY ( ';
      END IF;

      UTL_FILE.PUT_LINE(v_fA, v_line);

      v_constraint_name :=3D v_rec_fk.constraint_name;
	=09






      OPEN cr_max;
      FETCH cr_max INTO v_max_col;
      CLOSE cr_max;

      v_line :=3D'';
      FOR v_rec_column IN cr_column LOOP
		   v_line :=3D v_line || v_rec_column.column_name;
		   IF v_count >0 AND v_count < v_max_col THEN
				v_line :=3D v_line || ',' ;
	    	END IF;
		   v_count :=3D v_count + 1;
      END LOOP;

		v_count :=3D 1;
		v_line :=3D v_line || ') ';
      UTL_FILE.PUT_LINE(v_fA, v_line);

-- references part
		v_line :=3D 'REFERENCES ';


		v_line :=3D v_line || v_reference_table_name ||'(';

      FOR v_rec_ref_column IN cr_ref_columns LOOP
	   	v_line :=3D v_line || v_rec_ref_column.column_name;
	   	IF v_count >0 AND v_count < v_max_col THEN
				v_line :=3D v_line || ',' ;
	   	END IF;
	   	v_count :=3D v_count + 1;
      END LOOP;

		v_count :=3D 1;

-- add on delete clause
		IF  v_rec_fk.delete_rule =3D 'CASCADE' THEN=20
			v_line :=3D v_line || ') ON DELETE CASCADE ;';
	 	ELSE=20
			v_line :=3D v_line || ');';
		END IF;

		UTL_FILE.PUT_LINE(v_fA, v_line);	     =20


Received on Mon Jul 17 2000 - 11:25:03 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US