两个存在主外建关系的表空间，删其中一个是否需要添加cascade constraints的验证 - 重庆思庄Oracle、Redhat认证学习论坛

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

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

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

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

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

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

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

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

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';

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

说明：表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);

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.

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@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');

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

droptablespace app1tbs including contents and datafiles

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

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

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

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');

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';

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