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