constraints

最新推荐文章于 2022-12-23 14:05:06 发布
最新推荐文章于 2022-12-23 14:05:06 发布
阅读量1.9k 收藏
点赞数
分类专栏： oracle基础
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wll_1017/article/details/8621853
版权

1 、约束的类型

Table 5-1 Types of Constraints

Constraint TypeDescriptionSee Also

NOT NULL

Allows or disallows inserts or updates of rows containing a null in a specified column.

"NOT NULL Integrity Constraints"

Unique key

Prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

"Unique Constraints"

Primary key

Combines a NOT NULL constraint and a unique constraint. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

"Primary Key Constraints"

Foreign key

Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called thereferenced key.

"Foreign Key Constraints"

Check

Requires a database value to obey a specified condition.

"Check Constraints"

REF

Dictates types of data manipulation allowed on values in a REF column and how these actions affect dependent values. In an object-relational database, a built-in data type called aREF encapsulates a reference to a row object of a specified object type. Referential integrity constraints onREF columns ensure that there is a row object for theREF.

Oracle Database Object-Relational Developer's Guide to learn aboutREF constraints


2、NOT NULL:  非空约束

 非空约束要求表的一个列没有空值，默认情况下，表的所有行是可以为空的，null的数据类型为字符型，至于说是varchar还是char未知

2.1、非空约束的创建

建表时创建非空约束      

     SQL> create table rice (id number,                        

                name varchar2(2) not null);   

       表已创建。

建表后创建非空约束

     SQL> create table rice1 (id number,                                       

                                   name varchar2(2) );

  表已创建。

SQL> alter table rice1 modify name varchar2(2) not null;

表已更改。

  SQL> desc rice1

 名称                                                                             是否为空? 类型

 ------------------------------------------------------------------------------ -------- ----

 ID                                                                                    NUMBER

 NAME                                                                          NOT NULL VARCHAR2(2)

删除非空约束

  SQL> alter table rice1 modify name varchar2(2) null;
表已更改。

SQL> desc rice1
 名称                                                                                                      是否为空? 类型
 ----------------------------------------------------------------------------------------------------------------- -------- -------
 ID                                                                                                                 NUMBER
 NAME                                                                                                               VARCHAR2(2)

有非空约束的表，插入空值
SQL> insert into rice values(1,'');
insert into rice values(1,'')
                          *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."RICE"."NAME")

2.3 什么时候创建非空约束
 默认情况下，一个列上是可以包含空值的，为了保证那一列上不在具有空值，可以创建非空约束。
  （1）列必须是非空的情况
   （2）

  • You want to allow index scans of the table, or allow an operation that requires indexing all rows.

    Oracle Database indexes do not store keys whose values are all NULL. Therefore, for the preceding kinds of operations, at least one indexed column must have aNOTNULL constraint.

      我对这句话我总是很不理解，你想通过索引扫描整个表，必须添加非空约束


举例：

           SQL> create table rice(id number not null , name varchar2(5));

        表已创建。

        SQL> create table rice1(id number  , name varchar2(5));

        表已创建

        SQL> desc rice
       名称                                                                                       是否为空? 类型

       --------------------------------------------------------------------------------------------------------- ------- 

     ID                                                                                         NOT NULL NUMBE   

      NAME                                                                                     VARCHAR2(5)

          SQL >desc rice1    

           名称                                                                                                      是否为空? 类型         

         ----------------------------------------------------------------------------------------------------------------- -------- --------

                  ID                                                                                                       NUMBER                          

                  NAME                                                                                              VARCHAR2(5) 

SQL>  begin

  2   for i in 1..1000 loop

  3   insert into rice values(i,'ai'); 

4   commit;

  5   insert into rice1 values(i,'bi');

  6   commit;

  7   end loop; 

 8   end;

  9   /

PL/SQL 过程已成功完成。

SQL> create index ind_id on rice(id);

索引已创建。

SQL> create index ind_id1 on rice1(id);

索引已创建。

 

SQL>  select /*+ index(rice ind_id)*/count(*) from rice;

执行计划

----------------------------------------------------------

Plan hash value: 3112229557

-------------------------------------------------------------------

| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT |        |     1 |     8   (0)| 00:00:01 ||   1 | 

SORT AGGREGATE  |        |     1 |            |          ||   2 |  

 INDEX FULL SCAN| IND_ID |  1000 |     8   (0)| 00:00:01 |

-------------------------------------------------------------------

SQL>  select /*+ index(rice ind_id1)*/count(*) from rice1;

执行计划

----------------------------------------------------------

Plan hash value: 120591672

--------------------------------------------------------------------

| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |     1 |     3   (0)| 00:00:01 ||   1 | 

 SORT AGGREGATE    |       |     1 |            |          ||   2 |  

 TABLE ACCESS FULL| RICE1 |  1000 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------

结论：表rice上有非空约束，要求走索引，走了索引，但是rice2上没有非空约束，要求走索引没有走索引


3、Unique key: 唯一约束

（1）唯一约束要求一列或者组合列上是唯一的，没有重复的，唯一约束适合在任何列或组合列上建，当然这些列或组合列值不能重复。

（2）唯一约束和主键约束是不同的，因为空值永远满足唯一约束，而空值却不满足于主键约束

唯一约束的限制：

  • None of the columns in the unique key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE,OBJECT,REF,TIMESTAMPWITHTIMEZONE, or user-defined type. However, the unique key can contain a column ofTIMESTAMPWITHLOCALTIMEZONE.

  • A composite unique key cannot have more than 32 columns.

  • You cannot designate the same column or combination of columns as both a primary key and a unique key.

  • You cannot specify a unique key when creating a subview in an inheritance hierarchy. The unique key can be specified only for the top-level (root) view.


3.1建立唯一约束

建表时建立唯一约束

      SQL> create table rice(email varchar2(7)

                        constraint e_notnull not null ,

                         constraint e_uk unique(email));

     表已创建。

      SQL> insert into rice values('a');
      已创建 1 行。

      SQL> insert into rice values('a');
        insert into rice values('a')
         *
        第 1 行出现错误:
       ORA-00001: 违反唯一约束条件 (SCOTT.E_UK)


建表后建立唯一约束

             SQL> create table rice1(id number,name varchar(2));
            表已创建。

           SQL> alter table rice1 add constraint c_idd unique(id);
          表已更改。

删除唯一约束      
             SQL> alter table rice1 drop constraints c_idd;
            表已更改。


  3.2 唯一约束可以插入多个空值         

         SQL> drop table rice;          

    表已删除。

SQL> create table rice(id number constraint c_id not null,

                       name char(5) ,

                        constraint c_name unique(name));

表已创建。

      SQL> insert into rice values(1,'a');

已创建 1 行。

SQL> insert into rice values(1,'');

已创建 1 行。

SQL> insert into rice values(1,'');

已创建 1 行。

SQL> insert into rice values(2,'');

已创建 1 行。

SQL> select *From rice;     

   ID NAME

---------- -----    

     1 a      

   1      

   1     

    2


3.3、查看约束：

SQL>  select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,DEFERRABLE,DEFERRED From dba_constraints WHERE table_name='RICE';

OWNER                     CONSTRAINT_NAME                C TABLE_NAME                     DEFERRABLE     DEFERRED
---------------------------------------------- ------------------------------ - ------------------------------ -------------- ---------
SCOTT                                     C_ID                           C RICE                   NOT DEFERRABLE IMMEDIATE
SCOTT                                  C_NAME                       U RICE                   NOT DEFERRABLE IMMEDIATE

3.4、对比唯一约束和主键约束

          SQL> truncate table rice;

SQL> alter table rice add constraints pk_id1 primary key(id1)
  2  ;

SQL>  select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,DEFERRABLE,DEFERRED From dba_constraints WHERE table_name='RICE';

OWNER                                CONSTRAINT_NAME                C TABLE_NAME                     DEFERRABLE     DEFERRED
--------------------------------- ------------------------------ - ------------------------------ -------------- ---------
SCOTT                                   C_ID                           C RICE                   NOT DEFERRABLE IMMEDIATE
SCOTT                                  PK_ID1                         P RICE                   NOT DEFERRABLE IMMEDIATE
SCOTT                                 C_NAME                         U RICE                   NOT DEFERRABLE IMMEDIATE

主键约束显示为p 唯一约束显示为p

   id1 列建主键约束

   id列是非空约束

   name 是唯一约束

SQL> insert into rice values(1,'a',1);

已创建 1 行。

SQL> select *From rice;

        ID NAME         ID1
---------- ----- ----------
         1 a              1

主键约束不能为空

SQL> insert into rice values(1,null,null);
insert into rice values(1,null,null)
                               *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."RICE"."ID1")

主键是唯一的，不能重复

SQL> insert into rice values(1,null,1);
insert into rice values(1,null,1)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_ID1)

唯一约束列可以插入空值
                 SQL> insert into rice values(1,null,2);

               已创建 1 行。

唯一约束不能重复（空值比较特殊）

SQL> insert into rice values(1,'a',2);
insert into rice values(1,'a',2)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.C_NAME)


3.5 唯一索引和唯一约束的区别

（1）创建方法不一样

 SQL> create table rice(id number,id1 number);
表已创建。

SQL> create unique index u_id on rice(id);
索引已创建。

SQL> alter table rice add constraint c_id1 unique(id1);
表已更改。


（2）数据库在创建唯一约束时强制创建或者重复使用列上的索引，如果之前的列上没有索引，那么强制创建的索引是唯一索引，如果列上已存在索引，就重用存在的索引，不管此索引是否唯一。


建唯一约束自动创建唯一索引

QL> drop table rice;

表已删除。

QL> create table rice(id number,id1 number);

表已创建。

QL> alter table rice add constraints c_unique  unique (id);

表已更改。

SQL>  select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,DEFERRABLE,DEFERRED From dba_constraints WHERE table_name='RICE';

OWNER                                  CONSTRAINT_NAME                C TABLE_NAME                     DEFERRABLE     DEFERRED
-------------------------------------- ------------------------------ - ------------------------------ -------------- --------
SCOTT                                    C_UNIQUE                       U RICE                   NOT DEFERRABLE IMMEDIATE
SQL>  select INDEX_NAME,INDEX_TYPE,UNIQUENESS,STATUS From user_indexes where table_name='RICE';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
C_UNIQUE                       NORMAL                      UNIQUE    VALID

在列id上建立唯一索引，在列id1上建立普通索引，然后在两个列上个建立唯一约束，查看

SQL> alter table rice drop constraint c_unique;
已更改。

SQL> create unique index id_uniqe on rice(id);
索引已创建。

SQL> create  index id_nouniqe on rice(id1);
索引已创建。

SQL> alter table rice add constraint c_id unique(id);
表已更改。

SQL> alter table rice add constraint c_id1 unique(id1);
表已更改。

SQL>  select INDEX_NAME,INDEX_TYPE,UNIQUENESS,STATUS From user_indexes where table_name='RICE';
INDEX_NAME                     INDEX_TYPE                  UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
ID_NOUNIQE                     NORMAL                      NONUNIQUE VALID
ID_UNIQE                       NORMAL                      UNIQUE    VALID


SQL>  select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,DEFERRABLE,DEFERRED From dba_constraints WHERE table_name='RICE';

OWNER CONSTRAINT_NAME                C TABLE_NAME                     DEFERRABLE     DEFERRED
----- ------------------------------ - ------------------------------ -------------- ---------
SCOTT C_ID1                          U RICE                           NOT DEFERRABLE IMMEDIATE
SCOTT C_ID                           U RICE                           NOT DEFERRABLE IMMEDIATE

创建唯一约束重用列上以前存在的索引，在删约束的时候，不会自动删除存在的索引，如果是创建索引的时候，自动创建的唯一索引，在删除唯一约束的时候，自动删除唯一索引。

SQL> alter table rice drop constraints c_id;
表已更改。

SQL> alter table rice drop constraints c_id1;
表已更改。


SQL>  select INDEX_NAME,INDEX_TYPE,UNIQUENESS,STATUS From user_indexes where table_name='RICE';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
ID_NOUNIQE                     NORMAL                      NONUNIQUE VALID
ID_UNIQE                       NORMAL                      UNIQUE    VALID

结论：1、唯一约束是可以插入多个空值的

            2、唯一约束和唯一索引是不一样的


4、Primary key:主键约束

（1）主键唯一确定一行，一个表上只能有一个主键，主键可以确定主键列上是没有重复值，那一列或组合列不是空值。

（2）创建主键约束会隐式创建一个唯一索引和非空约束

 （3）如果在创建主键约束前，列上就已经存在索引，那么会重复使用存在的索引，如果不存在索引，那么会在那一列上建立一个唯一索引和约束同名。

       在删除主键约束时，如果是在创建主键是自动创建的唯一索引，删除约束时同时删除索引，如果创建主键约束时，重复使用已存在索引，删除约束时，索引仍然存在。


4.1创建主键

建表时创建主键

SQL> create table rice (id number primary key ,name varchar2(5));
表已创建。
SQL> insert into rice values(1,'a');
已创建 1 行。

SQL> insert into rice values(1,'a');
insert into rice values(1,'a')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0013515)


SQL> insert into rice values('','a');
insert into rice values('','a')
                       *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."RICE"."ID")

删除主键

SQL> alter table rice drop constraints SYS_C0013515;
表已更改。


建表后创建主键

方法一：

       SQL> alter table rice add constraints pk_id  primary key(id);
       表已更改。

也可以通过

方法二：

      SQL> create table rice1(id number,name varchar(10));
       表已创建。

       SQL> create unique index pk_id1 on rice1(id);
        索引已创建。

       SQL> alter table rice1 add  constraints pk_id1 primary key(id );
       表已更改。

两种方法的却别是： 在表比较大的时候可以通过方法二先创建唯一索引 加上online，这样可以不锁表，减少对表的读写堵塞，不影响业务。


SQL> select si.PROPERTY,ui.index_name
  2  from sys.ind$ si,dba_indexes ui,dba_objects uo
  3  where si.obj#=uo.OBJECT_ID
  4  and ui.index_name=uo.OBJECT_NAME
  5  and ui.table_name in ('RICE','RICE1');

  PROPERTY INDEX_NAME
---------- ------------------------------
      4097 PK_ID
         1 PK_ID1

Pk_id 对应的16进制为1001，有1000+1,即索引性质：unique+ The index was created by a constraint,而PK_id1  则为unique索引。



5、Foreign key:外键约束

当两张表有有相同的一列或者多列的时候，可以通过外键来连接两个表的关系，也可以叫做参照完整性约束，一个表的约束列必须参照另一个表相同列中定义的值。

外键可以使依赖另外一张表的某个列或多列，也可以是依赖本张表的某个列

The relational model permits the value of foreign keys to match either the referenced primary or unique key value, or be null


5.1外键的创建


SQL> create table t1 as select *From emp;
表已创建。

SQL> create table t2 as select *From dept;
表已创建。

SQL> select *From t1;
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
已选择12行。

SQL> select *From t2;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


SQL> alter table t1 add constraint  fk_t2 foreign key(deptno) references t2(deptno);
alter table t1 add constraint  fk_t2 foreign key(deptno) references t2(deptno)
                                                                       *
第 1 行出现错误:
ORA-02270: 此列列表的唯一关键字或主键不匹配


SQL> alter table t2 add constraint pk_t2 primary key(deptno);
表已更改。

SQL> alter table t1 add constraint  fk_t1 foreign key(deptno) references t2(deptno);
表已更改。

SQL> update t1 set deptno=null where deptno=10;
已更新3行。

SQL> select *From t1;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
      7839 KING       PRESIDENT            17-11月-81           5000
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300

已选择12行。



结论：(1)外键参考的表上的列必须有主键

           (2)外键可以为空

 

5.2  外键和主表的引用键在删除的时候会有一些矛盾，比如，在删除主表的引用键的行，或者修改主表引用键

DML Statements Allowed by Update and Delete No Action

DML StatementIssued Against Parent TableIssued Against Child Table

INSERT

Always OK if the parent key value is unique

OK only if the foreign key value exists in the parent key or is partially or all null

UPDATE NO ACTION

Allowed if the statement does not leave any rows in the child table without a referenced parent key value

Allowed if the new foreign key value still references a referenced key value

DELETE NO ACTION

Allowed if no rows in the child table reference the parent key value

Always OK

DELETE CASCADE

Always OK

Always OK

DELETE SET NULL

Always OK

Always OK

 

（1）删除主键，外键数据也级联删除

SQL> create table t1 (id number

                                     ,name varchar2(10),

                                     constraints pk_id primary key(id));
表已创建。


SQL> create table t2(id number,

                                   course varchar2(5),

constraints fk_id foreign key (id) references t1(id) on delete cascade);

表已创建。

SQL> insert into t1 values(1,'a');

已创建 1 行。

SQL> insert into t1 values(2,'b');

已创建 1 行。

SQL> insert into t1 values(3,'c');

已创建 1 行。

SQL> insert into t2 values(2,'maths');

已创建 1 行。

SQL> insert into t2 values(3,'maths');

已创建 1 行。

SQL> commit;

提交完成。


SQL> select *from t1;

        ID NAME
---------- --------------------
         1 a
         2 b
         3 c

SQL> select *from t2;

        ID COURSE
---------- ----------
         2 maths
         3 maths

SQL> update t1 set id=null where id=2;
update t1 set id=null where id=2
              *
第 1 行出现错误:
ORA-01407: 无法更新 ("SCOTT"."T1"."ID") 为 NULL


SQL> delete t1 where id=2;

已删除 1 行。

SQL> select *From t1;

        ID NAME
---------- --------------------
         1 a
         3 c

SQL> select *From t2;

        ID COURSE
---------- ----------
         3 maths

（2）主表删除，外键表设置成空

SQL> create table t1 (id number,

                                          name varchar2(10)  ,

                                           constraints pk_id primary key(id));

表已创建。

SQL> create table t2(id number,

                                           course varchar2(5),

                                       constraints fk_id foreign key (id) references t1(id) on delete set null);

表已创建。

 

SQL>  insert into t1 values(1,'a');

已创建 1 行。

SQL>  insert into t1 values(2,'b');

已创建 1 行。

SQL>  insert into t1 values(3,'c');

已创建 1 行。

SQL>  insert into t2 values(2,'maths');

已创建 1 行。

SQL>  insert into t2 values(3,'maths');

已创建 1 行。

SQL> commit;

提交完成。


SQL> select *from t1;

        ID NAME
---------- --------------------
         1 a
         2 b
         3 c

SQL> select *from t2;

        ID COURSE
---------- ----------
         2 maths
         3 maths

SQL> delete t1  where id=2;

已删除 1 行。

SQL> select *from t1;

        ID NAME
---------- --------------------
         1 a
         3 c

SQL> select *from t2;

        ID COURSE
---------- ----------
           maths
         3 maths


SQL> roll
回退已完成。
SQL> update t1 set id=4 where id=3;
update t1 set id=4 where id=3
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FK_ID) - 已找到子记录

 

5.3 锁和外键

在大多数情况下，用户需要在外键（foreign key）上创建索引。但有一种情况例外，当外键所引用的主键（primary key）或唯一键（unique key）从不更新或删除时，外键上可以不创建索引。 如果不在外键上建立索引，那么对主键更新时会对外键表瞬时产生一个4号锁，会阻止外键表的dml操作。 

如果外键上没有定义索引，对父表的主键执行 DML 操作时必须获得子表上的共享行排他表级锁（share row exclusive table lock）（也称为share-subexclusive table lock，SSX）。此锁能够阻止其他事务对子表执行 DML 操作。SSX 锁在获得后立即释放。如果父表中有多个主键值被更新或删除，对每行执行 DML 操作时都需要对子表进行一次加锁及解锁操作。
　如果外键上定义了索引，则对父表的主键执行 DML 操作时只需获得子表上的行共享表级锁（row share table lock）（也称为 subshare table lock，SS）。此锁不允许其他事务排他地对子表加锁，但允许对父表及子表执行 DML 操作。如果对子表进行更新操作的同时有可能存在对父表的更新或删除操作，则适宜采取本节讲述的锁机制。对父表的插入，更新，及删除操作无需获得子表上的锁，但更新及删除操作需要等待子表索引上的行级锁。

                 如果子表的外键约束定义中指定了 ON DELETE CASCADE 选项，则删除主表数据时将导致相关的子表数据同时被删除。在这种情况下，Oracle 采用的锁机制与用               户先手工地删除子表数据，再删除相关的主表数据时采用的锁机制相同。

6、Check: 检查约束
check约束是建立一列或者一组列上，要求条件为真，才能插入。

 

建表时创建check约束

SQL> create table t1(id number check(id>1 and id<10),

                                    id1 number check(id1 in (1,2)));

表已创建。

SQL> insert into t1 values(11,1)
  2  ;
insert into t1 values(11,1)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0013556)


SQL> insert into t1 values(10,1)
  2  ;
insert into t1 values(10,1)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0013556)

SQL> insert into t1 values(9,1)
  2  ;

已创建 1 行。

 

指定约束名：

SQL> create table t2(x varchar2(2),constraints ckeck_x check(x='f'or x='m'));

表已创建。

 

删除约束：

SQL> alter table t2 drop constraints  ckeck_x;

表已更改。

SQL> insert into t2 values('m');

已创建 1 行。

commit；

建表后创建约束：

SQL> alter table t2 add constraints ckeck_a check(x in ('a','b'));
alter table t2 add constraints ckeck_a check(x in ('a','b'))
                               *
第 1 行出现错误:
ORA-02293: 无法验证 (SCOTT.CKECK_A) - 违反检查约束条件


SQL> select *From t2;

X
--
m

因为表中已经存在数据，再建约束和以前的约束不一样，所以会报错


SQL> alter table t2 add constraints ckeck_a check(x in ('a','b')) novalidate;

表已更改。

SQL> insert into t2 values('y');
insert into t2 values('y')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CKECK_A)


SQL> insert into t2 values('a');

已创建 1 行。

这时可以加novalidate，使得约束对已存在的数据不检测，只检查新添加进来的数据。

 

7、REF:

 

 

8 约束的状态 

你可以指定一个约束用来约束现有数据或是以后新插入的数据

If you change the state of any single constraint from ENABLE NOVALIDATE to ENABLE VALIDATE, then the operation can be performed in parallel, and does not block reads, writes, or other DDL operations.

 

你不能让外键引用一个关闭的主键或者唯一键

Modified DataExisting DataSummary

ENABLE

VALIDATE

Existing and future data must obey the constraint. An attempt to apply a new constraint to a populated table results in an error if existing rows violate the constraint.

ENABLE

NOVALIDATE

The database checks the constraint, but it need not be true for all rows. Thus, existing rows can violate the constraint, but new or modified rows must conform to the rules.

DISABLE

VALIDATE

The database disables the constraint, drops its index, and prevents modification of the constrained columns.

DISABLE

NOVALIDATE

The constraint is not checked and is not necessarily true.

If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specifyKEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.

如果你打开唯一索引或者主键约束，在那一列上没有索引，那么oracle会创建一个唯一索引。除非你在关闭约束的时候指定keep index。这个索引会随着约束关闭而删除，随着约束的启用而打开

 

（1）enable+validate  对表中现有数据以及后来的数据都会检测，如果有违反约束的，就会报错。

（2）enable+novalidate 对表中已经存在的数据不检测，对以后的数据insert 或者update会检测

（3）disable+validate 数据库关闭约束，删除约束列上的索引，阻止修改约束列的值，但是约束是合法的，多用在数据仓库中，可以让你节省索引的空间

（4）disable+novalidate 约束不会检测

You cannot drop a table whose primary key is being referenced by a foreign key even if the foreign key constraint is inDISABLENOVALIDATE state. Further, the optimizer can use constraints inDISABLENOVALIDATE state.

 

SQL> create table t1(id number check(id>1 and id<10),

                                    id1 number check(id1 in (1,2)));

表已创建。

 

SQL> select constraint_name,constraint_type,STATUS ,VALIDATED,DEFERRABLE from dba_constraints where table_name='T1';

CONSTRAINT_NAME                C STATUS   VALIDATED     DEFERRABLE
------------------------------ - -------- ------------- --------------
SYS_C0013557                   C ENABLED  VALIDATED     NOT DEFERRABLE
SYS_C0013556                   C ENABLED  VALIDATED     NOT DEFERRABLE

SQL> alter table t1 disable constraints SYS_C0013557;

表已更改。

SQL> select *From t1;

        ID        ID1
---------- ----------
         9          1

SQL> select constraint_name,constraint_type,STATUS ,VALIDATED,DEFERRABLE from dba_constraints where table_name='T1';

CONSTRAINT_NAME                C STATUS   VALIDATED     DEFERRABLE
------------------------------ - -------- ------------- --------------
SYS_C0013556                   C ENABLED  VALIDATED     NOT DEFERRABLE
SYS_C0013557                   C DISABLED NOT VALIDATED NOT DEFERRABLE

如果你在关闭约束的时候，没有指定约束的状态，关闭约束同时状态也变成not validate

SQL> alter table t1 disable validate constraints SYS_C0013557;

表已更改。

SQL> select constraint_name,constraint_type,STATUS ,VALIDATED,DEFERRABLE from dba_constraints where table_name='T1';

CONSTRAINT_NAME                C STATUS   VALIDATED     DEFERRABLE
------------------------------ - -------- ------------- --------------
SYS_C0013556                   C ENABLED  VALIDATED     NOT DEFERRABLE
SYS_C0013557                   C DISABLED VALIDATED     NOT DEFERRABLE

SQL> update t1 set id1=1 where id=2;
update t1 set id1=1 where id=2
*
第 1 行出现错误:
ORA-25128: 不能对带有禁用和验证约束条件 (SCOTT.SYS_C0013557) 的表进行插入/更新/删除

 

说明 约束列disable+validate状态不能修改约束列的值

 

SQL> select INDEX_NAME,STATUS From dba_indexes where table_name='T1';

INDEX_NAME                     STATUS
------------------------------ --------
PK_T1                          VALID

SQL> select constraint_name,constraint_type,STATUS ,VALIDATED,DEFERRABLE from dba_constraints where table_name='T1';

CONSTRAINT_NAME                C STATUS   VALIDATED     DEFERRABLE
------------------------------ - -------- ------------- --------------
PK_T1                          P ENABLED  VALIDATED     NOT DEFERRABLE

SQL> alter table t1 disable constraints pk_t1;

表已更改。

SQL> select INDEX_NAME,STATUS From dba_indexes where table_name='T1';

未选定行

SQL> select constraint_name,constraint_type,STATUS ,VALIDATED,DEFERRABLE from dba_constraints where table_name='T1';

CONSTRAINT_NAME                C STATUS   VALIDATED     DEFERRABLE
------------------------------ - -------- ------------- --------------
PK_T1                          P DISABLED NOT VALIDATED NOT DEFERRABLE

 

说明：关闭约束会删除索引

SQL> alter table t1 disable constraints pk_t1 keep index;

表已更改。

SQL> select INDEX_NAME,STATUS From dba_indexes where table_name='T1';

INDEX_NAME                     STATUS
------------------------------ --------
PK_T1                          VALID

SQL> select constraint_name,constraint_type,STATUS ,VALIDATED,DEFERRABLE from dba_constraints where table_name='T1';

CONSTRAINT_NAME                C STATUS   VALIDATED     DEFERRABLE
------------------------------ - -------- ------------- --------------
PK_T1                          P DISABLED NOT VALIDATED NOT DEFERRABLE

SQL> insert into t1 select *from emp;
insert into t1 select *from emp
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_EMPNO)

结论：通过keep index 命令可以保留索引，在开启约束的时候可以不用建索引，但是保留索引，索引是唯一索引保证了索引的唯一性

 

9、延迟约束

每一个约束要么是延迟的，要么是非延迟的，这种状态决定了约束的合法性，

如果是非延迟约束，会在每个语句执行时候检测合法性，如果违反约束则回滚。

如果是延迟约束，可以分为两种，一种是INITIALLY IMMEDIATE 一种是INITIALLY DEFERRED，延迟约束允许用户使用set constraints 使用约束在commited的时候检测约束。延迟约束只是让你暂时关闭约束，进行更改可能违反约束的数据，直到没有违反约束。

      你不能更改延迟性约束，除非删除重建。 不能把延迟性约束通过alter 变成no deferred

        INITIALLY IMMEDIATE:会在语句执行的时候检测约束

         INITIALLY DEFERRED：在语句执行的时候不检测，但是在commite的时候会检测约束，如果违反约束会回退。

建立延迟约束：

SQL> create table tt(id number ,constraints un_id unique(id) initially deferred deferrable);

表已创建。

SQL>  select TABLE_NAME, constraint_name,constraint_type,STATUS ,VALIDATED,DEFERRABLE,DEFERRED from dba_constraints where table_name='TT';
TABLE_NAME CONSTRAINT CO STATUS           VALIDATED                  DEFERRABLE                   DEFERRED
---------- ---------- -- ---------------- -------------------------- ---------------------------- ------------------
TT         UN_ID      U  ENABLED          VALIDATED                  DEFERRABLE                   DEFERRED

 

SQL> select INDEX_NAME,INDEX_TYPE ,UNIQUENESS from dba_indexes where table_name='TT';

INDEX_NAME                                                   INDEX_TYPE                                     UNIQUENESS
------------------------------------------------------------ ------------------------------------------------------ ----
UN_ID                                                        NORMAL                                         NONUNIQUE

SQL> insert into t1 values(1);
insert into t1 values(1)
            *
第 1 行出现错误:
ORA-00947: 没有足够的值


SQL> insert into tt values(1);

已创建 1 行。

SQL> insert into tt values(1);

已创建 1 行。

SQL> commit;
commit
*
第 1 行出现错误:
ORA-02091: 事务处理已回退
ORA-00001: 违反唯一约束条件 (SCOTT.UN_ID)

 


SQL> create table tt1(id number ,constraints un_id1 unique(id) initially immediate deferrable);

表已创建。

SQL>  select TABLE_NAME, constraint_name,constraint_type,STATUS ,VALIDATED,DEFERRABLE,DEFERRED from dba_constraints where table_name='TT1';

TABLE_NAME CONSTRAINT CO STATUS           VALIDATED                  DEFERRABLE                   DEFERRED
---------- ---------- -- ---------------- -------------------------- ---------------------------- ------------------
TT1        UN_ID1     U  ENABLED          VALIDATED                  DEFERRABLE                   IMMEDIATE


SQL> select INDEX_NAME,INDEX_TYPE ,UNIQUENESS from dba_indexes where table_name='TT1';

INDEX_NAME                                                   INDEX_TYPE                                     UNIQUENESS
------------------------------------------------------------ ------------------------------------------------------ --------
UN_ID1                                                       NORMAL                                         NONUNIQUE

 

SQL> insert into tt1 values(1);

已创建 1 行。

SQL> insert into tt1 values(1);
insert into tt1 values(1)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.UN_ID1)


结论：延迟约束，上的索引都是非唯一索引；

 

 


SQL> create table t1(id number not null deferrable);

表已创建。

SQL> create table t2(id number not null );

表已创建。

SQL> create index id_t1 on t1(id);

索引已创建。

SQL> create index id_t2 on t2(id);

索引已创建。

SQL> insert into t1 values(1);

已创建 1 行。

SQL> select *from t1;

        ID
----------
         1

SQL> select *from t2;

        ID
----------
         1

SQL> set autot trace
SQL> select  count(*) from t2;


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'ID_T2' (INDEX) (Cost=2 Card=1
          )


SQL> select  count(*) from t1;


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=3 Card=1)


 

结论：非空的非延迟约束+索引在select country（*） from t 时会走索引；如果是非空的延迟约束存在+索引是不会走索引的；

 

遗留问题：（1）外键和锁的试验

                    （2）关闭主键约束保留唯一索引，仍然可以保证唯一性，但是不能保证空值，那么关闭约束的意义在哪？

 

wluckdog
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
airflow的安装文件constraints-3.8.txt
04-09
airflow的安装文件constraints-3.8.txt
Oracle10g实现只读表的N种方法
congjiumi6955的博客
08-04 215
有时为了提高数据的安全性，我们需要把一个或多个表设置为只读，即不允许对其执行任何 DML(Insert, Update, Delete) 操作。 在Oracle11g中实现只读表非常简单，只需要...
Oracle数据库基础
君只见独不见的专栏
11-28 471
一、数据表管理 二、完整性约束 三、约束的验证状态
约束的4种状态以及immediate、deferred
11-27 877
--整理以前的学习笔记 1、约束的4种状态： enable validate/disable novalidate/enable novalidate/disable validate cre...
什么是回表查询
热门推荐
我真的睡不够
09-11 2万+
什么是回表查询？ 这先要从InnoDB的索引实现说起，InnoDB有两大类索引： 聚集索引(clustered index) 普通索引(secondary index) InnoDB聚集索引和普通索引有什么差异？ InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引： （1）如果表定义了PK，则PK就是聚集索引； （2）如果表没有定义PK，则第一个not NULL unique列是聚集索引； （3）否则，InnoDB会创建一个隐..
唯一性索引（Unique Index）与普通索引（Normal Index）差异（上）
ciqu9915的博客
06-16 1329
索引是我们经常使用的一种数据库搜索优化手段。适当的业务操作场景使用适当的索引方案可以显著的提升系统整体性能和用户体验。在Oracle中，索引有包括很多类型。不同类型的索引适应不同的系统环境和访问场景。其中，唯一性索引Uni...
ug625-Constraints Guide.pdf
04-18
《UG625约束指南》会详细阐述如何定义这些约束，包括如何使用Xilinx的专用语法和工具，例如UCF（用户约束文件）或XDC（Xilinx Design Constraints）文件来指定约束。这些文件包含了关于设计的各种信息，如输入/输出...
Synopsys_Timing_Constraints_and_Optimization_User_Guide.pdf
12-27
Synopsys Timing Constraints and Optimization User Guide Timing Constraints（时序约束）是数字集成电路设计中的一种重要技术，用于描述和约束数字电路的时序行为。 Synopsys Timing Constraints and ...
predictive control with constraints
12-13
在"predictive control with constraints"这一主题中，我们将深入探讨如何在满足特定约束条件下进行有效的预测控制。 预测控制的核心是预测模型，它可以是基于动态方程、状态空间模型或者数据驱动的模型。这个模型...
论文研究-An Efficient Trip Planning Algorithm Under Constraints.pdf
08-16
一种支持约束关系的高效的行程规划算法，鲍金玲，杨晓春，行程规划问题的研究已经成为人们关注的热点之一，越来越多的人需要这一服务的帮助来确定最优的行程路线。假如用户指定了源点和终
oracle中索引的类型,oracle索引类型normal
weixin_40008135的博客
04-08 2322
BASED NORMAL NORMAL/REV UNIQUENES ---NONUNIQUE NONUNIQUE NONUNIQUE UNIQUE INDEX_NAME ---EMP_ENAME_INDEX EMP_JOB_BMP IDX_ENAME PK_EMP 如上所示, INDEX_NAME 用于标识索引名。 INDEX_TYPE 用于标识索引类型: NORMAL 表示普通......我们可...
Oracle：6、索引
博风_刺耳的博客
04-10 723
索引（使用物理地址rowID）与索引组织表（把整个记录作为索引内容）
Oracle基础 之 约束条件状态
甲骨文可乐的专栏
07-08 2027
以下是CONCEPT的文档描述： Constraint States ■ ENABLE ensures that all incoming data conforms to the constraint ■ DISABLE allows incoming data, regardless of whether it conforms to the constraint ■ VALIDATE ens
关于数据库中UNIQUE唯一约束可否有多个null值问题
qq_36511225的博客
05-22 1万+
我们都知道unique约束,限制此字段在数据库表中此字段值唯一 但是如果我们不传值,那么他默认为null的话,可以有多个吗? 伟大的毛主席告诉我们:实践是检验真理的唯一标准! 本次测试使用的是数据库时MySQL 5.5 首先创建一张表将username字段设置成unique约束 CREATE TABLE test_unique ( #主键 uid INT PRIMARY KEY auto...
Linux数据库报错ora-12528,奇怪的ORA-12528错误
weixin_35225264的博客
05-02 969
奇怪的ORA-12528错误个人在配置两个数据库，在NET Manger 中，2个服务名，配置一个监听。两个服务的端口号都为1521，链接类型：数据库默认。监听位置：TCP/IP 1521数据库服务：全局数据库名：ORCLORACLE主目录：d:oracleproduct10.1.0db_1SID:ORCL另一个数据库类似配置以下是从网上找来。http://kenlan.itpub.net/pos...
plsql 只读_Oracle10g实现只读表的N种方法
weixin_28728031的博客
01-17 488
有时为了提高数据的安全性，我们需要把一个或多个表设置为只读，即不允许对其执行任何DML(Insert, Update, Delete)操作。在Oracle11g中实现只读表非常简单，只需要执行alter table ... readonly;语句即可；但是在11g之前的版本，“只读”只对数据库和表空间有效，如果我们要实现一个只读表，只能通过其他办法。下面就介绍在Oracle10g中实现只读表的几种...
oracle fetch time,ORA-25228: timeout or end-of-fetch
weixin_42524883的博客
04-15 318
hican anybody solve my problem, plzmy script:1-destination script:create type mytype as object (n number);/Begindbms_aqadm.create_queue_table('queue_table','mytype',multiple_consumers => true);End;...
约束(CONSTRAINT)
zhujianxi的专栏
03-06 816
--============================= --SQL基础--> 约束(CONSTRAINT) --=============================   一、几类数据完整性     实体完整性：表中记录不重复(任何两条记录不全等)并且每条记录都有一个非空主键     域完整性：表中字段值必须与字段数据类型、格式、有效范围相吻合     参照完整性：不能引
【MySQL】唯一性约束【UNIQUE】从基础到就业
weixin_59131972的博客
12-23 7742
唯一约束 【unique】：用来限制某个字段/某列的值不能重复，唯一约束允许出现 多个空值 同一个表可以有多个唯一约束；唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。 唯一性约束允许列值为空；在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。 你给表创建唯一约束的时候，mysql会默认创建一个唯一索引。设置了唯一索引的列可以插入 NULL 值，但不允许出现一张表中，某一列，设置了唯一约束，插入两条 NULL
epipolar constraints
最新发布
09-09
Epipolar constraints是用于计算多个图像间的具有对应关系的特征点的一种方法。根据引用中提到的文献，epipolar constraints是通过对图像中的特征点进行关联和匹配来获得两幅图像间的对应关系。具体来说，当两条直线...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值