万恶之源SQL--主外键约束

约束 （071考题）

生产中会应用很多约束：

 最常用的就是主键约束，用来唯一标识数据表中的某一列，同时也限定了他的属性：非空和唯一

 外键约束：用来跟主键连接产生关系的列，不唯一

 例如下：创建一张表并带有主外键约束条件：

并使主键失效with  cascade

恢复主键：without  cascade

TONY@ prod>CREATE TABLE emp (emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, 
  2  ename VARCHAR2 (15),
  3  salary NUMBER (8, 2),
  4  mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp (emp_no));

表已创建。

TONY@ prod>select TABLE_NAME,TABLE_TYPE,INDEX_NAME,INDEX_TYPE from user_indexes;

TABLE_NAME                                                                                 
TONY@ prod>col TABLE_NAME for a15;
TONY@ prod>col OWNER for a15
TONY@ prod>col CONSTRAINT_NAME for a15
TONY@ prod>col table_name for a15;
TONY@ prod>select CONSTRAINT_NAME,OWNER ,TABLE_NAME from user_constraints;

CONSTRAINT_NAME OWNER           TABLE_NAME
--------------- --------------- ---------------
EMP_MGR_FK      TONY            EMP
EMP_EMP_NO_PK   TONY            EMP

TONY@ prod>select TABLE_NAME,TABLE_TYPE,INDEX_NAME,INDEX_TYPE from user_indexes;

TABLE_NAME      TABLE_TYPE                        INDEX_NAME                                                                                 INDEX_TYPE
--------------- --------------------------------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------
EMP3            TABLE                             PK_EMPNO                                                                                   NORMAL
EMP             TABLE                             EMP_EMP_NO_PK                                                                              NORMAL


TONY@ prod>select TABLE_NAME,TABLE_TYPE,INDEX_NAME,INDEX_TYPE,GENERATED,status from user_indexes;

TABLE_NAME      TABLE_TYPE                        INDEX_NAME                                                                                 INDEX_TYPE                                                                        GEN STATUS
--------------- --------------------------------- ------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------- --- ------------------------
EMP3            TABLE                             PK_EMPNO                                                                                   NORMAL                                                                            N   VALID
EMP             TABLE                             EMP_EMP_NO_PK                                                                              NORMAL                                                                            N   VALID

TONY@ prod>
TONY@ prod>
TONY@ prod>
TONY@ prod>alter table  emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

表已更改。

TONY@ prod>select TABLE_NAME,TABLE_TYPE,INDEX_NAME,INDEX_TYPE,GENERATED,status from user_indexes;

TABLE_NAME      TABLE_TYPE                        INDEX_NAME                                                                                 INDEX_TYPE                                                                        GEN STATUS
--------------- --------------------------------- ------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------- --- ------------------------
EMP3            TABLE                             PK_EMPNO                                                                                   NORMAL                                                                            N   VALID

TONY@ prod>desc user_constraints;
 名称                                                                                                                                                                        是否为空? 类型
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------------------------------------------------
 OWNER                                                                                                                                                                                  VARCHAR2(30)
 CONSTRAINT_NAME                                                                                                                                                               NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                                                                                                                                                        VARCHAR2(1)
 TABLE_NAME                                                                                                                                                                    NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                                                                                                                                                       LONG
 R_OWNER                                                                                                                                                                                VARCHAR2(30)
 R_CONSTRAINT_NAME                                                                                                                                                                      VARCHAR2(30)
 DELETE_RULE                                                                                                                                                                            VARCHAR2(9)
 STATUS                                                                                                                                                                                 VARCHAR2(8)
 DEFERRABLE                                                                                                                                                                             VARCHAR2(14)
 DEFERRED                                                                                                                                                                               VARCHAR2(9)
 VALIDATED                                                                                                                                                                              VARCHAR2(13)
 GENERATED                                                                                                                                                                              VARCHAR2(14)
 BAD                                                                                                                                                                                    VARCHAR2(3)
 RELY                                                                                                                                                                                   VARCHAR2(4)
 LAST_CHANGE                                                                                                                                                                            DATE
 INDEX_OWNER                                                                                                                                                                            VARCHAR2(30)
 INDEX_NAME                                                                                                                                                                             VARCHAR2(30)
 INVALID                                                                                                                                                                                VARCHAR2(7)
 VIEW_RELATED                                                                                                                                                                           VARCHAR2(14)

TONY@ prod>select CONSTRAINT_NAME,OWNER ,TABLE_NAME,INDEX_NAME,INVALID,status from user_constraints;

CONSTRAINT_NAME OWNER           TABLE_NAME      INDEX_NAME                                                                                 INVALID               STATUS
--------------- --------------- --------------- ------------------------------------------------------------------------------------------ --------------------- ------------------------
EMP_MGR_FK      TONY            EMP                                                                                                                              DISABLED
EMP_EMP_NO_PK   TONY            EMP                                                                                                                              DISABLED

TONY@ prod>ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk;

表已更改。


此时外键仍是disable状态，因为恢复主键时候未加cascade。



TONY@ prod>select CONSTRAINT_NAME,OWNER ,TABLE_NAME,INDEX_NAME,INVALID,status from user_constraints;

CONSTRAINT_NAME OWNER           TABLE_NAME      INDEX_NAME                                                                                 INVALID               STATUS
--------------- --------------- --------------- ------------------------------------------------------------------------------------------ --------------------- ------------------------
EMP_EMP_NO_PK   TONY            EMP             EMP_EMP_NO_PK                                                                                                    ENABLED
EMP_MGR_FK      TONY            EMP                                                                                                                              DISABLED

TONY@ prod>

恢复外键

TONY@ prod>alter table emp enable CONSTRAINT  EMP_MGR_FK;

表已更改。

TONY@ prod>select CONSTRAINT_NAME,OWNER ,TABLE_NAME,INDEX_NAME,INVALID,status from user_constraints;

CONSTRAINT_NAME OWNER           TABLE_NAME      INDEX_NAME                                                                                 INVALID               STATUS
--------------- --------------- --------------- ------------------------------------------------------------------------------------------ --------------------- ------------------------
EMP_MGR_FK      TONY            EMP                                                                                                                              ENABLED
EMP_EMP_NO_PK   TONY            EMP             EMP_EMP_NO_PK                                                                                                    ENABLED

TONY@ prod>

Jrojyun

2021-03-31