1. FOREIGN KEY 정보 보기
SELECT A.TABLE_NAME AS "TABLE_NAME"
, A.CONSTRAINT_NAME AS "KEY_NAME"
, B.TABLE_NAME AS "REFERENCING_TABLE"
, B.CONSTRAINT_NAME AS "FOREIGN_KEY_NAME"
, B.STATUS AS "FK_STATUS"
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
ORDER BY 1, 2, 3, 4;
2. FOREIGN KEY 해제
SELECT 'ALTER TABLE ' || B.OWNER || '.' || B.TABLE_NAME || ' DROP CONSTRAINT ' || B.CONSTRAINT_NAME || ';'
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
AND A.TABLE_NAME = 'table_name';
3. CONSTRAINT_TYPE 종류
C Check on a table Column
O Read Only on a view Object
P Primary Key Object
R Referential AKA Foreign Key Column
U Unique Key Column
V Check Option on a view Object
[출처] FOREIGN KEY 정보 보기 및 FOREIGN KEY 해제|작성자 천상천하