This tip is useful for extracting CREATE scripts for foreign key constraints belonging to the current user. It may come handy in situations such as recreating the database at a different location, while specifying deletion rules, etc. It has been tested on both 8i and 9i.
set serveroutput on SIZE 50000 set linesize 1000 set head off declare mPos Number; mRefTab varchar2(200); cursor cCOn is SELECT B.table_name table_name , a.constraint_name constraint_name ,B.POSITION POSITION, b.column_name column_name , a.r_constraint_name r_constraint_name from user_constraints a inner join user_cons_columns b on a.constraint_name = b.constraint_name where constraint_type='R' order by b.constraint_name,b.position; begin FOR mCon in cCon Loop IF mcon.position = 1 then DBMS_OUTPUT.PUT_LINE( ' ALTER TABLE ' || MCON.table_name ); DBMS_OUTPUT.PUT_LINE( ' ADD CONSTRAINT ' || mcon.constraint_name || ' FOREIGN KEY (' ); END IF; SELECT MAX(d.POSITION) into mPos from user_cons_columns d WHERE d.constraint_name = mcon.constraint_name; select table_name into mRefTab from user_constraints where constraint_name=mcon.r_constraint_name; IF mcon.position < mpos then DBMS_OUTPUT.PUT_LINE( ' ' || mcon.column_name || ' , '); ELSE DBMS_OUTPUT.PUT_LINE( mcon.column_name || ' ) REFERENCES ' ) ; DBMS_OUTPUT.PUT_LINE( mRefTab || ' ON DELETE CASCADE ; ' || CHR(10) ) ; END IF; End Loop; End ; /
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
This was first published in August 2003