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