oracle中的约束

oracle中的约束。unique与主键的一个主要的差别就是唯一约束可以插入多个null，主键必须是唯一，并且不能是null

在唯一约束与主键约束上面oracle都会默认有一个唯一性索引
外键是建立在子表上来约束子表的插入更新和父表的删除更新
references后面就是参考的主表departments

约束的几种类型
c- check
p - primary key

u-unique
r- references
v -view with check option
o -read only on a view

not null就是check

SQL> create view v as select * from t where x>0 with check option;

视图已创建。

SQL> insert into v values(1);

已创建 1 行。

SQL> insert into v values(-1);
insert into v values(-1)
            *
第 1 行出现错误:
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
VRM_MARK                       F_VRM_1                        R
V                              SYS_C0019576                   V

已选择35行。

with check option的作用
这个视图只是加了限制约束，只能插入x>0的数据

SQL> create view v1 as select * from t where x>0;

视图已创建。
SQL> insert into v1 values(-29);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from v1;

         X
----------
         1

SQL> select * from t;

         X
----------
         1
        -1
       -29

可以看到，在没有with check option的时候，是可以通过视图插入任何值的，插入完成后，再次通过视图查询，还是能查出视图定义的数据，
插入的不符合定义的数据无法显示，在基表中可以看到插入的数据

只读视图约束

SQL> create view v2 as select * from t with read only;

视图已创建。

SQL> select table_name,constraint_name,constraint_type from user_constraints where table_name ='V2';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
V2                             SYS_C0019577                   O

SQL>

通过唯一性索引实现唯一性约束

SQL> create unique index t on test(case when id=0 then val1 end,case when id=0 then val2 end);

索引已创建。

SQL> insert into test values(0,1,1);

已创建 1 行。

SQL> insert into test values(0,1,1);
insert into test values(0,1,1)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (BAIXYU.T)


SQL>

主外键关系中，如果子表中的关联列上面没有索引，子表在dml操作中的同时，父表也在dml操作，那么附表会被阻塞。下面的例子：

SQL> create table t1(x int,y int,constraint t1_unq unique(x,y));
SQL> create table t2(x int,y int,constraint t2_fk_t1 foreign key(x,y) references t1(x,y) on delete cascade);

会话一：

SQL> select * from t1;

         X          Y
---------- ----------
         1          1
         1
         2

SQL> select * from t2;

         X          Y
---------- ----------
         1          1
         1

SQL> delete from t1 where y=1;

已删除 1 行。


会话二：下面的语句被阻塞
SQL> insert into t2 values(1,1);


SQL> select (select username from v$session where sid = a.sid) blocker,
  2         a.sid,
  3         ' is blocking ',
  4         (select username from v$session where sid = b.sid) blockee,
  5         b.sid
  6    from v$lock a, v$lock b
  7   where a.block = 1
  8     and b.request > 0
  9     and a.id1 = b.id1
 10     and a.id2 = b.id2;

BLOCKER                               SID 'ISBLOCKING'  BLOCKEE                               SID
------------------------------ ---------- ------------- ------------------------------ ----------
SCOTT                                 199  is blocking  SCOTT                                 136

要是在子表的关联列上面创建了索引后，在dml的时候就没有请求阻塞了。

分组函数对于null的处理是直接忽略

建议主外键关系的组合列，设置not null
存在null的情况,即使是级联的删除也会导致子表中存在孤儿记录。

SQL> select * from t2;

         X          Y
---------- ----------
         1
         1          1

SQL> select  * from t1;

         X          Y
---------- ----------
         1
         1          1


SQL> delete t1 where y is null;

已删除 1 行。

SQL> selelct * from t2;
SP2-0734: 未知的命令开头 "selelct * ..." - 忽略了剩余的行。
SQL> select * from t2;

         X          Y
---------- ----------
         1
         1          1