Extract CREATE scripts for foreign key constraints
This tip is useful for extracting CREATE scripts for foreign key constraints belonging to the current user.
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.