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就是checkSQL> 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 ---------- 1SQL> 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 2SQL> select * from t2;

X          Y

---------- ----------         1          1 1SQL> 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          1SQL> select  * from t1;

X          Y

---------- ----------         1 1          1SQL> delete t1 where y is null;

已删除 1 行。

SQL> selelct * from t2;

SP2-0734: 未知的命令开头 "selelct * ..." - 忽略了剩余的行。

SQL> select * from t2;

X          Y

---------- ----------         1 1          1