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

denglj

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

数据库：oracle11.2.0.4 64位

1、环境准备

sys@ORCL2022-10-19 20:49:15> create tablespace app1tbs datafile'/u01/app/oracle/oradata/orcl/app1tbs.dbf' size 10m;

Tablespacecreated.

sys@ORCL2022-10-19 20:50:32> create tablespace app2tbs datafile'/u01/app/oracle/oradata/orcl/app2tbs.dbf' size 20m;

Tablespacecreated.

sys@ORCL2022-10-19 20:53:02> alter user scott account unlock;

Useraltered.

sys@ORCL2022-10-19 20:53:19> alter user scott identified by tiger;

Useraltered.

sys@ORCL2022-10-19 20:53:31> create table scott.app1_emp tablespace app1tbs asselect * from scott.emp;

Tablecreated.

sys@ORCL2022-10-19 20:54:55> create table scott.app2_dept tablespace app2tbs asselect * from scott.dept;

Table created.

scott@ORCL2022-10-19 20: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-0100: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-2300:00:00       1300                    10

14 rowsselected.

scott@ORCL2022-10-19 20:17:45> alter table app2_dept add constraint PK_Test_ID primarykey(deptno);

scott@ORCL2022-10-19 20:41:32> col column_name for a20

scott@ORCL2022-10-19 20:41:44> select a.constraint_name,a.column_name fromuser_cons_columns a,user_constraints b wherea.constraint_name=b.constraint_name and b.constraint_type='P' anda.table_name='APP2_DEPT';

CONSTRAINT_NAME                COLUMN_NAME

--------------------------------------------------

PK_TEST_ID                     DEPTNO

说明：表APP2_DEPT中字段DEPTNO为主键.

scott@ORCL2022-10-19 20:53:27> alter table app1_emp add constraint fk_app1_deptnoforeign key (deptno) references app2_dept(deptno);

Tablealtered.

scott@ORCL2022-10-19 21:10:17> selectowner,constraint_name,constraint_type,table_name,delete_rule,last_change fromuser_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-19 20:58:37

说明：表APP1_EMP(表空间:APP1TBS)字段DEPTNO为外键，引用表APP2_DEPT(表空间:APP2TBS)中的主键字段DEPTNO.环境准备OK.

2、相关查询

根据外键约束名查列名.

scott@ORCL2022-10-19 21:10:18> select * from user_cons_columns whereconstraint_name='FK_APP1_DEPTNO';

OWNER      CONSTRAINT_NAME                TABLE_NAME      COLUMN_NAME            POSITION

---------------------------------------- --------------- -------------------- ----------

SCOTT      FK_APP1_DEPTNO                 APP1_EMP        DEPTNO                        1

根据外键约束名查引用的哪个表.

scott@ORCL2022-10-19 21:11:14> select table_name from user_constraints whereconstraint_name=(select r_constraint_name from user_constraints whereconstraint_name='FK_APP1_DEPTNO');

TABLE_NAME

---------------

APP2_DEPT

3、删除测试

测试删除表空间APP1TBS.

scott@ORCL2022-10-19 21:15:06> drop tablespace app1tbs including contents anddatafiles;

droptablespace app1tbs including contents and datafiles

*

ERROR atline 1:

ORA-01031:insufficient privileges

sys@ORCL2022-10-19 21:16:00> grant drop tablespace to scott;

Grantsucceeded.

scott@ORCL2022-10-19 21:17:12> drop tablespace app1tbs including contents anddatafiles;

Tablespacedropped.

scott@ORCL2022-10-19 21:17:38> select * from user_cons_columns whereconstraint_name='FK_APP1_DEPTNO';

norows selected

scott@ORCL2022-10-19 21:18:21> select table_name from user_constraints whereconstraint_name=(select r_constraint_name from user_constraints whereconstraint_name='FK_APP1_DEPTNO');

norows selected

scott@ORCL2022-10-19 21:18:28> selectowner,constraint_name,constraint_type,table_name,delete_rule,last_change fromuser_constraints c where c.constraint_type='R' and c.table_name='APP1_EMP';

norows selected

结论：如上所示，两个存在主外建关系的表空间，删其中一个不需要添加cascadeconstraints.