Here is how to use a self-join of the view USER_CONSTRAINTS to find constraints that is referring to a specific table. Note that you can also use the DBA_CONSTRAINTS if logged in as a privileged user.
First, set up a couple of extra tables in the SCOTT schema to illustrate the point (the existing SCOTT objects will contain only one - 1 - Foreign Key by default):
CONNECT SCOTT/TIGER
CREATE TABLE PORTS
(
PORT_ID NUMBER,
PORT_NAME VARCHAR2 (20),
COUNTRY VARCHAR2 (40),
CAPACITY NUMBER
);
CREATE TABLE SHIPS
(
SHIP_ID NUMBER,
SHIP_NAME VARCHAR2 (20),
CAPACITY NUMBER,
LENGTH NUMBER,
HOME_PORT_ID NUMBER
);
ALTER TABLE SHIPS
ADD CONSTRAINT PK_SHIPS PRIMARY KEY (SHIP_ID);
ALTER TABLE PORTS
ADD CONSTRAINT PK_PORTS PRIMARY KEY (PORT_ID);
ALTER TABLE SHIPS
ADD CONSTRAINT SHIPS_PORTS_FK FOREIGN KEY (HOME_PORT_ID)
REFERENCES PORTS (PORT_ID);
Now, let's find the relationships between the Foreign key constraints and their corresponding unique key constraint:
CONNECT SCOTT/TIGER
SET LINES 200
SET HEADING OFF
SET FEEDBACK OFF
SET PAGES 0
SELECT B.CONSTRAINT_NAME
|| ' on table '
|| B.TABLE_NAME
|| ' points to table '
|| A.TABLE_NAME
"Relationship"
FROM USER_CONSTRAINTS A
FULL OUTER JOIN USER_CONSTRAINTS B
ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
WHERE A.OWNER = 'SCOTT' AND B.R_CONSTRAINT_NAME IS NOT NULL;
Here you can see the relationships between the constraints and their tables:| Relationship |
|---|
| SHIPS_PORTS_FK on table SHIPS points to table PORTS |
| FK_DEPTNO on table EMP points to table DEPT |
In my case I wanted to drop the constraints, so I concatenated the columns with strings to generate a "drop constraint" script:
CONNECT SCOTT/TIGER
SELECT 'alter table '
|| B.OWNER
|| '.'
|| B.TABLE_NAME
|| ' drop constraint '
|| B.CONSTRAINT_NAME
|| ';'
FROM USER_CONSTRAINTS A
FULL OUTER JOIN USER_CONSTRAINTS B
ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
WHERE A.OWNER = 'SCOTT' AND B.R_CONSTRAINT_NAME IS NOT NULL;
Returned rows:alter table SCOTT.SHIPS drop constraint SHIPS_PORTS_FK; alter table SCOTT.EMP drop constraint FK_DEPTNO;
Note that you can achieve the same goal by using a subquery instead of a FULL OUTER JOIN:
CONNECT SCOTT/TIGER
SELECT OWNER,TABLE_NAME,CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE='P'
AND OWNER = 'SCOTT'
);
Output:
| OWNER | TABLE_NAME | CONSTRAINT_NAME |
|---|---|---|
| SCOTT | EMP | FK_DEPTNO |
| SCOTT | SHIPS | SHIPS_PORTS_FK |
