文档课题：两个存在主外建关系的表空间，删其中一个是否需要添加cascade constraints的验证.
数据库：oracle 11.2.0.4 64位
1、环境准备
sys@ORCL 2022-10-14 09:49:15> create tablespace app1tbs datafile '/u01/app/oracle/oradata/orcl/app1tbs.dbf' size 10m;
Tablespace created.
sys@ORCL 2022-10-14 09:50:32> create tablespace app2tbs datafile '/u01/app/oracle/oradata/orcl/app2tbs.dbf' size 20m;
Tablespace created.
sys@ORCL 2022-10-14 09:53:02> alter user scott account unlock;
User altered.
sys@ORCL 2022-10-14 09:53:19> alter user scott identified by tiger;
User altered.
sys@ORCL 2022-10-14 09:53:31> create table scott.app1_emp tablespace app1tbs as select * from scott.emp;
Table created.
sys@ORCL 2022-10-14 09:54:55> create table scott.app2_dept tablespace app2tbs as select * from scott.dept;
Table created.
scott@ORCL 2022-10-14 10:10:48> select * from app1_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
scott@ORCL 2022-10-14 10:17:45> alter table app2_dept add constraint PK_Test_ID primary key(deptno);
scott@ORCL 2022-10-14 10:41:32> col column_name for a20
scott@ORCL 2022-10-14 10:41:44> select a.constraint_name,a.column_name from user_cons_columns a,user_constraints b where a.constraint_name=b.constraint_name and b.constraint_type='P' and a.table_name='APP2_DEPT';
CONSTRAINT_NAME COLUMN_NAME
------------------------------ --------------------
PK_TEST_ID DEPTNO
说明：表APP2_DEPT中字段DEPTNO为主键.
scott@ORCL 2022-10-14 10:53:27> alter table app1_emp add constraint fk_app1_deptno foreign key (deptno) references app2_dept(deptno);
Table altered.
scott@ORCL 2022-10-14 11:10:17> select owner,constraint_name,constraint_type,table_name,delete_rule,last_change from user_constraints c where c.constraint_type='R' and c.table_name='APP1_EMP';
OWNER CONSTRAINT_NAME C TABLE_NAME DELETE_RU LAST_CHANGE
---------- ------------------------------ - --------------- --------- -------------------
SCOTT FK_APP1_DEPTNO R APP1_EMP NO ACTION 2022-10-14 10:58:37
说明：表APP1_EMP(表空间:APP1TBS)字段DEPTNO为外键，引用表APP2_DEPT(表空间:APP2TBS)中的主键字段DEPTNO.环境准备OK.
2、相关查询
根据外键约束名查列名.
scott@ORCL 2022-10-14 11:10:18> select * from user_cons_columns where constraint_name='FK_APP1_DEPTNO';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
---------- ------------------------------ --------------- -------------------- ----------
SCOTT FK_APP1_DEPTNO APP1_EMP DEPTNO 1
根据外键约束名查引用的哪个表.
scott@ORCL 2022-10-14 11:11:14> select table_name from user_constraints where constraint_name=(select r_constraint_name from user_constraints where constraint_name='FK_APP1_DEPTNO');
TABLE_NAME
---------------
APP2_DEPT
3、删除测试
测试删除表空间APP1TBS.
scott@ORCL 2022-10-14 11:15:06> drop tablespace app1tbs including contents and datafiles;
drop tablespace app1tbs including contents and datafiles
*
ERROR at line 1:
ORA-01031: insufficient privileges
sys@ORCL 2022-10-14 10:10:00> grant drop tablespace to scott;
Grant succeeded.
scott@ORCL 2022-10-14 11:17:12> drop tablespace app1tbs including contents and datafiles;
Tablespace dropped.
scott@ORCL 2022-10-14 11:17:38> select * from user_cons_columns where constraint_name='FK_APP1_DEPTNO';
no rows selected
scott@ORCL 2022-10-14 11:18:21> select table_name from user_constraints where constraint_name=(select r_constraint_name from user_constraints where constraint_name='FK_APP1_DEPTNO');
no rows selected
scott@ORCL 2022-10-14 11:18:28> select owner,constraint_name,constraint_type,table_name,delete_rule,last_change from user_constraints c where c.constraint_type='R' and c.table_name='APP1_EMP';
no rows selected
结论：如上所示，两个存在主外建关系的表空间，删其中一个不需要添加cascade constraints.
