Oracle 基础篇 --- 约束-白红宇

Oracle 基础篇 --- 约束

阅读量：6213 次

发布时间：2019-06-21

本文共 6766 字，大约阅读时间需要 22 分钟。

##第三. 约束

###3.1 创建约束

--创建用户CREATE TABLE emp1 (   emp1_id NUMBER (6) CONSTRAINT emp1_pk PRIMARY KEY,   first_name VARCHAR2 (20),   last_name VARCHAR2 (25) CONSTRAINT emp1_last_name_nn NOT NULL,   email VARCHAR2 (25)         CONSTRAINT emp1_email_nn NOT NULL CONSTRAINT emp_email_uk UNIQUE,   phone_numbe VARCHAR2 (20),   hire_date DATE CONSTRAINT emp1_hire_date_nn NOT NULL,   job_id VARCHAR2 (10) CONSTRAINT emp1_job_id_nn NOT NULL,   salary NUMBER (8, 2) CONSTRAINT emp1_salary_ck CHECK (salary > 0),   commission_pct NUMBER (2, 2),   manager_id NUMBER (6)         CONSTRAINT emp1_manager_pk REFERENCES employees (employee_id),   department_id NUMBER (4)         CONSTRAINT emp1_dept_fk REFERENCES departments (department_id));-- 插入合法数据insert into emp1 values (198, 'Tony', 'Wang','TonyMail',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10);--插入主键冲突数据insert into emp1 values (198, 'Tony1', 'Wang1','TonyMail_1',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10);ERROR at line 1:ORA-00001: unique constraint (HR.EMP1_PK) violated--插入email 不唯一的数据insert into emp1 values (800, 'Tony1', 'Wang1','TonyMail',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10);ERROR at line 1:ORA-00001: unique constraint (HR.EMP1_EMAIL_UK) violated--插入job_id 为空的数据insert into emp1 values (800, 'Tony1', 'Wang1','TonyMail1',888888,to_date('2011-07-01','YYYY-MM-DD'),'',100000,0.8,100,10);ERROR at line 1:ORA-01400: cannot insert NULL into ("HR"."EMP1"."JOB_ID")-- 插入 salary 为负数insert into emp1 values (800, 'Tony1', 'Wang1','TonyMail1',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',-100000,0.8,100,10);ERROR at line 1:ORA-02290: check constraint (HR.EMP1_SALARY_CK) violated-- 输入 manager_id 父键不存在insert into emp1 values (800, 'Tony1', 'Wang1','TonyMail1',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,10,10);ERROR at line 1:ORA-02291: integrity constraint (HR.EMP1_MANAGER_PK) violated - parent key not found

###3.2 约束状态

此处输入图片的描述

**DISABLE NOVALIDATE：**不检查新数据和现有数据，因此这些数据可能不符合约束条件。当数据来自验证过的源，而且表是只读表时，通常会使用此状态。因此，不会将新数据输入表中。在已清理了数据的数据仓库环境中使用NOVALIDATE。此时不需要进行验证，因而可以节省很多时间。

**DISABLE VALIDATE：**如果约束条件处于此状态，则不允许对有约束条件的列进行任何修改。因为如果在验证现有数据后，又允许将未经检查的数据输入表中，就会出现不一致的情况。通常，在必须验证现有数据但不进行修改时，以及不需要索引来提高性能时，可使用此状态。

**ENABLE NOVALIDATE：**新数据符合约束条件，但现有数据处于未知状态。通常在确信表中只存在纯净数据和符合规则的数据的情况下使用该状态，此时不需要进行验证。但是，不允许将违反规则的新数据输入到系统中。

**ENABLE VALIDATE：**新数据与现有数据均符合约束条件。这是约束条件的典型状态和默认状态。

##1. disable novalidateSQL> alter table emp1  2  disable novalidate constraint emp1_email_uk;Table altered.SQL> insert into emp1 values (199, 'Tony', 'Wang','TonyMail',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10);1 row created.SQL> select EMP1_ID, LAST_NAME, EMAIL, SALARY, DEPARTMENT_ID from emp1;   EMP1_ID LAST_NAME  EMAIL          SALARY DEPARTMENT_ID---------- ---------- ---------- ---------- -------------       198 Wang       TonyMail       100000            10       199 Wang       TonyMail       100000            10SQL> delete from emp1 where emp1_id = 199;1 row deleted.SQL> commit;##2.disable validateSQL> alter table emp1  2  disable validate constraint emp1_email_uk;Table altered.SQL> insert into emp1 values (199, 'Tony', 'Wang','TonyMail',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10);insert into emp1 values (199, 'Tony', 'Wang','TonyMail',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10)*ERROR at line 1:ORA-25128: No insert/update/delete on table with constraint (HR.EMP1_EMAIL_UK) disabled and validated##3.enable novalidateSQL> alter table emp1  2  enable novalidate constraint emp1_email_uk;Table altered.SQL> insert into emp1 values (199, 'Tony', 'Wang','TonyMail',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10);insert into emp1 values (199, 'Tony', 'Wang','TonyMail',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10)*ERROR at line 1:ORA-00001: unique constraint (HR.EMP1_EMAIL_UK) violated##4.enable validateSQL> alter table emp1  2  enable validate constraint emp1_email_uk;Table altered.SQL> select owner, constraint_name, constraint_type, table_name, status, validated from user_constraints where table_name = 'EMP1' and constraint_name = 'EMP1_EMAIL_UK';OWNER      CONSTRAINT_NAME C TABLE_NAME STATUS   VALIDATED---------- --------------- - ---------- -------- -------------HR         EMP1_EMAIL_UK   U EMP1       ENABLED  VALIDATED

###3.3 约束条件检查

检查约束条件的时机：

执行语句时（对于非延迟约束条件）

发出COMMIT 时（对于延迟约束条件）

###添加延迟约束 SQL> alter table emp1  2  add score number(2) constraint emp1_score_ck check(score>0) initially deferred deferrable;Table altered.###查看约束状态select constraint_name, constraint_type,status,deferrable,deferred,validated from user_constraints  2  where constraint_name = 'EMP1_SCORE_CK' ;CONSTRAINT_NAME      CONSTRAINT_TYPE STATUS   DEFERRABLE     DEFERRED  VALIDATED-------------------- --------------- -------- -------------- --------- -------------EMP1_SCORE_CK        C               ENABLED  DEFERRABLE     DEFERRED  VALIDATED###插入小于0的数据SQL> insert into emp1 values (199, 'Tony1', 'Wang1','TonyMail1',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10,-10);1 row created.SQL> commit;commit*ERROR at line 1:ORA-02091: transaction rolled backORA-02290: check constraint (HR.EMP1_SCORE_CK) violated###修改约束为非延迟SQL> alter table emp1  2  modify constraint emp1_score_ck initially immediate;Table altered.select constraint_name, constraint_type,status,deferrable,deferred,validated from user_constraints  2  where constraint_name = 'EMP1_SCORE_CK' ;CONSTRAINT_NAME      CONSTRAINT_TYPE STATUS   DEFERRABLE     DEFERRED  VALIDATED-------------------- --------------- -------- -------------- --------- -------------EMP1_SCORE_CK        C               ENABLED  DEFERRABLE     IMMEDIATE VALIDATEDinsert into emp1 values (199, 'Tony1', 'Wang1','TonyMail1',888888,to_date('2011-07-01','YYYY-MM-DD'),'MK_MAN',100000,0.8,100,10,-10)*ERROR at line 1:ORA-02290: check constraint (HR.EMP1_SCORE_CK) violated#note: You cannot change the state of a NOT DEFERRABLE constraint to INITIALLY DEFERRED.select constraint_name, constraint_type,status,deferrable,deferred,validated from user_constraints  2   where constraint_name = 'EMP1_SCORE_CK' ;CONSTRAINT_NAME      CONSTRAINT_TYPE STATUS   DEFERRABLE     DEFERRED  VALIDATED-------------------- --------------- -------- -------------- --------- -------------EMP1_SCORE_CK        C               ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATEDSQL> alter table emp1  2  modify constraint emp1_score_ck initially deferred deferrable;modify constraint emp1_score_ck initially deferred deferrable                                                   *ERROR at line 2:ORA-00933: SQL command not properly ended

转载于:https://my.oschina.net/wangbinbin0326/blog/472080

你可能感兴趣的文章

微信支付获取不到OPENID跳转永远失败的原因 CENTOS6.5 WDCP面板

查看>>

关于inline函数

查看>>

Windows内核新手上路1——挂钩SSDT