ocp-047-61

最新推荐文章于 2021-04-04 11:35:51 发布

五岳山

最新推荐文章于 2021-04-04 11:35:51 发布

阅读量948

点赞数

分类专栏： ocp考试

本文链接：https://blog.csdn.net/dbaherbert/article/details/9717745

版权

ocp考试专栏收录该内容

61 篇文章 0 订阅

订阅专栏

TABLE command.

D. It would remain disabled and can be enabled only by dropping the foreign

key constraint and recreating it.

实验

CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk;

SQL> select t.table_name,
2 t.constraint_name,
3 t.constraint_type,
4 t.status,
5 t.deferrable,
6 t.deferred,
7 t.validated
8 from user_constraints t
9 where table_name = 'EMP'
10 ;

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------ ------------------------------ --------------- -------- -------------- --------- -------------
EMP EMP_EMP_NO_PK P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
EMP EMP_MGR_FK R ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SQL> ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

Table altered

SQL> select t.table_name,
2 t.constraint_name,
3 t.constraint_type,
4 t.status,
5 t.deferrable,
6 t.deferred,
7 t.validated
8 from user_constraints t
9 where table_name = 'EMP'
10 ;

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------ ------------------------------ --------------- -------- -------------- --------- -------------
EMP EMP_EMP_NO_PK P DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED
EMP EMP_MGR_FK R DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

SQL> ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk;

Table altered

SQL>
SQL> select t.table_name,
2 t.constraint_name,
3 t.constraint_type,
4 t.status,
5 t.deferrable,
6 t.deferred,
7 t.validated
8 from user_constraints t
9 where table_name = 'EMP'
10 ;

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------ ------------------------------ --------------- -------- -------------- --------- -------------
EMP EMP_EMP_NO_PK P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
EMP EMP_MGR_FK R DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

61. Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp (
emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp
);
创建表时声明的主键约束和外键约束自动生效

ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
disable主键约束时使用cascade会使这个主键上的外键约束同时失效

ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk;
enable主键约束使之生效，但是外键约束还是失效状态

What would be the status of the foreign key EMP_MGR_FK?
A. It would be automatically enabled and deferred.
外键约束是失效状态，并且没有声明deferred的时候应该是nodeferrable immediate状态

B. It would be automatically enabled and immediate.
外键约束是失效状态，并且没有声明deferred的时候应该是nodeferrable immediate状态

C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.(right)
需要手动显式声明

D. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
不用将约束删除并重新建立，只要通过alter table就可以了