ocp-047-61

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; 
ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk; 
What would be the status of the foreign key EMP_MGR_FK? 
A. It would be automatically enabled and deferred. 
B. It would be automatically enabled and immediate. 
C. It would remain disabled and has to be enabled manually usin g the ALTER 

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就可以了