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 |
No comments:
Post a Comment