约束Constraint

1 什么是约束

强制性：向表中插入数据的时候，oracle对数据进行检查，满足条件才能插入

完整性：比如身份证号码必须唯一，重复的不允许插入

5种约束：非空、唯一、主键、外键、检查

　　　　not null、unique、primary key、foreign key、check

约束是一种限制，插入或修改数据时进行判断

在针对定义了约束的表执行DML操作时，如果违反约束，将自动回滚出现问题的整个语句，而不是语句中的单个操作，也不是整个事务。

创建时机： 1.创建表的时候，约束和表一起创建

　　　　　　2.在表创建后给alter table语句给表添加约束

约束存储在数据字典中，USER_CONSTRAINTS

2 定义一个约束

列级约束：引用单个列，在列的内部定义，可以定义任何类型完整性约束

语法：create table table_name(col1 col_type CONSTRAINT constraint_name constraint_type,col2 col_type,...);

例子：

create table employees(e_id number(6) CONSTRAINT emp_e_id_pk primary key,ename varchar2(20));

表级约束：可以引用多个列，与列的定义分开，可以定义除not null之外的约束

语法：create table table_name(col1 col_type,col2 col_type,...,CONSTRAINT constraint_name constraint_type (col1));

例子：

create table employees2(e_id number(6),ename varchar2(20),CONSTRAINT emp_e_id_pk2 primary key (e_id));

3 not null非空约束

not null非空约束：保证表中的某些列不出现空值

注意：

　　NOT NULL非空约束只能在列级定义，不能在表级定义

　　其他约束，如果定义在一列上，两种都可以

create table emp01(
    empno number(4),
    ename varchar2(10) constraint nn_emp01_ename not null,
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal number(7,2),
    comm number(7,2),
    deptno number(7,2) not null)
;

insert into emp01(empno,ename) values(4157,'TOM');

注意：

　　使用子查询创建表时，列上的not null(非空)约束也将传递到新表，

　　但primary key(主键)、unique(唯一)、foreigne key(外键)约束以及隐式的not null(主键列)约束都不会被子表继承

create table emp02 as select * from emp01;

4 unique唯一键约束

UINQUE约束可以在表级或者列级定义

表级unique约束：

create table dept01(
    deptno number(2),
    dname varchar2(14),
    loc varchar2(13),
    constraint uk_dept01_dname unique (dname))
;

insert into dept01 select * from dept;
insert into dept01 select * from dept;

有唯一键约束的列不可以插入重复的值，但可以插入一个或多个空值

insert into dept01 values (50,null,null);
insert into dept01 values (60,null,null);
select * from dept01;

5 primary key主键约束

主键是表中的一列或者多列的组合，能够唯一标识表中的一行

一个表只能有一个主键，可以有多个唯一或非空约束

主键约束可以在表级或者列级定义

表级PRIMARY KEY约束和列级UINQUE约束混合使用：

create table dept02(
    deptno number(2),
    dname varchar2(14) constraint uk_dept02_dname unique,
    loc varchar2(13),
    constraint pk_dept02_deptno primary key (deptno))
    ;

insert into dept02 select * from dept;
insert into dept02 values(20,'MARKTING','DALLAS');
insert into dept02 values(null,'FINANCE','NEW YORK');

主键约束和唯一键约束需要索引，如果不存在，oracle会自动创建索引

col INDEX_NAME for a20
col INDEX_TYPE for a20
col TABLE_NAME for a20
select index_name,index_type,table_name from user_indexes;

主键约束 = 非空约束 + 唯一键约束

primary key = not null + unique

6 foreign key外键约束

【说明】

emp表的deptno列值参考dept表的deptno列

emp是子表，dept是父表

emp表中插入数据时，deptno必须是存在于父表deptno列中的值

外键约束可以在表级定义也可以在列级定义

表级别外键约束：

create table emp02(
    empno number(4),
    ename varchar2(10) not null,
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal number(7,2),
    comm number(7,2),
    deptno number(7,2) not null,
    constraint fk_emp02_deptno foreign key (deptno) references dept02 (deptno))
;

insert into emp02 values(7571,'FORD','MANAGER',null,sysdate,1000,200,9);
insert into emp02 values(7571,'FORD','MANAGER',null,sysdate,1000,200,20);
commit;
select * from emp02;

【说明】

emp02表DEPTNO=9，不在主键中（10~50）parent key not found

外键约束定义在子表上，所参考的父表列需要有唯一约束或主键约束

有外键约束的列允许插入空值

insert into emp02 values(7572,'ALICE','MANAGER',null,sysdate,1000,200,null);

直接插入不允许，是因为emp02的deptno列有not null约束，删除not约束并重新插入：

alter table emp02 modify deptno null;
insert into emp02 values(7572,'ALICE','MANAGER',null,sysdate,1000,200,null);

6.1 on delete cascade/set null

foreign key约束关键字：

foreign key：在表级指定子表中的列
references：标识在父表中的列
on delete cascade：当父表中的列被删除，子表中相对应得列也被删除
on delete set null：子表中相应的列置空

6.2 不指定on delete cascade

默认不指定on delete cascade时，不能删除父表中已经被子表引用的行：

select * from dept02;
select * from emp02;
delete from dept02 where deptno=20;
delete from dept02 where deptno=10;
rollback;

【说明】

违反了完整性约束，不允许删除dept02表中deptno=20的行。父表中没有被子表引用的行可以被删除

6.3 指定on delete cascade

约束不能修改，只能创建、删除、添加

删除约束、重新添加约束：

alter table emp02 drop constraint fk_emp02_deptno;
alter table emp02 add constraint fk_emp02_deptno foreign key (deptno) references dept02 (deptno) on delete cascade;

delete from dept02 where deptno=20;
select * from emp02;
rollback;

删除父表中行时，子表emp02中引用父表的行deptno=20也一起被删除

6.4 指定on delete set null

alter table emp02 drop constraint fk_emp02_deptno;
alter table emp02 add constraint fk_emp02_deptno foreign key(deptno) references dept02(deptno) on delete set null;

select * from emp02;
delete from dept02 where deptno=20;
select * from emp02;
rollback;

【说明】

删除父表中的行，子表中引用父表行的外键列为null

7 check约束

check约束：

定义每一行必须满足的条件
以下的表达式是不允许的：
- 　　出现currval，nextval，level，rownum伪列
- 　　使用sysdate，uid，user，userenv函数
- 　　在查询中涉及到其他行的值

check约束通常是对某一列的值做出限制

一个列上可以定义多个check约束

check约束可以定义在列级或者表级

create table dept03(deptno number(2) constraint ck_dept03_deptno check (deptno between 10 and 99));

insert into dept03 values(5);
insert into dept03 values(10);
commit;
select * from dept03;

8 增加一个约束

可以增加、删除一个约束，但不能更改约束
指定一个约束是有效还是无效
可以用MODIFY子句为一个表的列增加NOTNULL约束

语法：alter table table_name add CONSTRAINT constraint_name constraint_type (column);

8.1 自引用外键约束

自引用外键约束是外键约束的变体：外键约束的父表和子表是同一个表

emp表中，经理mgr本身也必须是一名员工empno

alter table emp02 add constraint fk_emp02_mgr foreign key (mgr) references emp02(empno);

外键约束引用的父表列，必须存在unique或primary key约束

alter table emp02 add constraint pk_emp02_empno primary key (empno);
alter table emp02 add constraint fk_emp02_mgr foreign key (mgr) references emp02(empno);

8.2 增加非空约束

增加非空约束要用modify

alter table emp02 modify empno not null;

9 删除一个约束

语法：alter table table_name drop CONSTRAINT constraint_name;

alter table emp02 drop constraint fk_emp02_mgr;

9.1 cascade

alter table dept02 drop constraint pk_dept02_deptno;

alter table dept02 drop constraint pk_dept02_deptno cascade;

删除父表上的主键约束时，级联把子表的外键约束先删除

9.2 删除非空约束

alter table emp02 modify empno null;

10 级联约束cascade constraints

删除有约束的列(表)时报错，加cascade constraints可先把约束删除

10.1 删除列级alter table drop column ... cascade constraint

alter table dept02 add constraint pk_dept02_deptno primary key (deptno);
alter table emp02 add constraint fk_emp02_deptno foreign key (deptno) references dept02 (deptno);

alter table dept02 drop column deptno;

alter table dept02 drop column deptno cascade constraint;

10.2 drop table ... cascade constraint

drop table dept02;
create table dept02 as select * from dept;
alter table dept02 add constraint pk_dept02_deptno primary key (deptno);
alter table emp02 add constraint fk_emp02_deptno foreign key (deptno) references dept02 (deptno);

drop table dept02;
drop table dept02 cascade constraint;

11 与约束有关的数据字典

user_constraints
user_cons_colums

col CONSTRAINT_NAME for a30
col CONSTRAINT_TYPE for a10
col TABLE_NAME for a40
col R_CONSTRAINT_NAME for a30
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME from user_constraints;

col owner for a10
col CONSTRAINT_NAME for a25
col TABLE_NAME for a10
col column_name for a15
select * from user_cons_columns;

12 约束的状态

12.1 使约束失效

alter table dept03 disable constraint ck_dept03_deptno;
alter table dept03 modify constraint ck_dept03_deptno disable;

select constraint_name,table_name,status,validated from user_constraints where table_name='DEPT03';

12.2 使约束生效

alter table dept03 enable constraint ck_dept03_deptno;

select constraint_name,table_name,status,validated from user_constraints where table_name='DEPT03';

enable validate：新插入数据和表中已有数据都满足约束

disable novalidate：可以插入任何数据，表中可以存在不满足约束的数据

enable novalidate：新插入数据必须满足约束，但表中可以存在不满足约束的数据

　　（主键/唯一键约束如果设置为此状态，创建时需使用 deferrable）

enable：启用约束（默认）

disable：禁用约束

validate：表中已有数据验证（默认）

novalidate：表中已有数据不验证

disable validate：这种情况最终结果是DML命令锁定表：表中所有数据都符合约束，新插入数据可以不满约束，逻辑上矛盾，此时insert/update/delete都不允许

alter table dept03 disable validate constraint ck_dept03_deptno;

13 总结

约束类型：not null、unique、primary key、foreign key、check

列级：not null

表级或列级：uinque、primary key、foreign key、check

创建约束、删除约束、增加约束（增加、删除非空约束）

posted @ 2021-05-11 18:16 chchcharlie、阅读(273) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

chchcharlie、

不求便于他人，只求便于自己......

约束Constraint

约束Constraint

1 什么是约束

2 定义一个约束

3 not null非空约束

4 unique唯一键约束

5 primary key主键约束

6 foreign key外键约束

6.1 on delete cascade/set null

6.2 不指定on delete cascade

6.3 指定on delete cascade

6.4 指定on delete set null

7 check约束

8 增加一个约束

8.1 自引用外键约束

8.2 增加非空约束

9 删除一个约束

9.1 cascade

9.2 删除非空约束

10 级联约束cascade constraints

10.1 删除列级alter table drop column ... cascade constraint

10.2 drop table ... cascade constraint

11 与约束有关的数据字典

12 约束的状态

12.1 使约束失效

12.2 使约束生效

13 总结

公告