两个存在主外建关系的表空间，删其中一个是否需要添加cascade constraints的验证

文档课题：两个存在主外建关系的表空间，删其中一个是否需要添加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.