Oracle约束操作
约束是在表中定义的用于维护数据库完整性的一些规则通过为表中的字段定义约束可以防止将错误的数据插入到表中
注意
如果某个约束只作用于单独的字段既可以在字段级定义约束也可以在表级定义约束;但如果某个约束将作用于多个字段必须在表级定义约束
oracle中的约束通过名称来进行识别在定义约束时可以通过constraint关键字为约束命名如果用户没有为约束指定名称oracle将自动为约束建立默认的名称
主键约束(primary key)
主键约束的特点
定义为主键约束的字段中不能包含任何重复值并且不能包含null值
同个表中只能定义个主键约束
可以为一个字段定义主键约束也可以为多个字段的组合定义主键约束
oracle会自动为具有主键约束的字段建立个唯一索引和个非空约束
例如
create table person
(
p_id int primary key 定义该字段为主键约束
p_name varchar()
p_age int
constraint p_pk primary key (p_id)
);
create table person
(
p_id int constraint p_pk primary key 定义该字段为主键约束并指定约束名字
p_name varchar()
p_age int
constraint p_pk primary key (p_id)
);
create table person
(
p_id int
p_name varchar()
p_age int
constraint p_pk primary key (p_idp_name)定义复合主键并指定名字
primary key(p_idp_name)
);
非空约束(not null)
非空约束的特点
定义了非空约束的字段中不能包含null值
只能在字段级定义非空约束
在同一个表中可以定义多个非空约束
例如
create table person
(
p_id int
p_name varchar() not null 定义该列的非空约束
p_age int
);
唯一约束(unique):
唯一约束的特点
定义了唯一约束的字段中不能包含重复值
可以为个字段定义唯一约束也可以为多个字段的组合定义唯一约束因此唯一约束既可以定义在字段级也可以定义在表级
oracle会自动为具有唯一约束的字段建立个唯一索引
对同一字段可以同时定义非空和唯一约束
如果在个字段上仅定义了唯一约束而没有定义非空约束则该字段可以包含多个null值
例如
create table person
(
p_id int
p_name varchar() unique定义该列的唯一约束
p_age int
);
create table person
(
p_id int
p_name varchar() constraint p_un unique not null 同时定义唯一和非空约束
p_age int
);
create table person
(
p_id int
p_name varchar()
p_age int
constraint p_un unique(p_name) 指定约束名字
);
外键约束(foreign key)
外键约束的特点
定义为外键约束的字段中只能包含相应的其他表中引用字段的值或null值
可以为个字段定义外键约束也可以为多个字段的组合定义外键约束
定义了外键约束的字段和相应的引用字段可以存在于同个表中称为自引用
对同个字段可以同时定义外键和非空约束
主表中的被引用列必须有主键约束或唯一约束
例如
create table person
(
p_id int
p_name varchar()
p_age int
w_id int
constraint p_fk foreign key (w_id) references works(w_id) 外键约束
);
create table person
(
p_id int
p_name varchar()
p_age int
w_id int constraint p_fk references works 外键约束引用works表中的主键
);
create table person
(
p_id int
p_name varchar()
p_age int
w_id int references works 外键约束
w_id int constraint w_fk references works(w_id)
);
在定义外键约束时还可以通过on关键字来指定引用行为的类型当主表中的一条记录被删除时需要通过引用行为来确定如何处理子表中的外键列的值
删除子表中所有相关的记录(delete cascade)
将所有相关记录的外键值设置为null(delete set null)
检查约束(check)
检查约束的特点
在检查约束的表达式中必须引用到表中的一个或多个字段并且表达式的计算结果必须是一个布尔值
在表达式中不能包含子查询
在表达式中不能包含sysdateuiduseruserenv等sql函数也不能包含rowidrownum等伪列
检查约束可以在字段级和表级定义
对同一个字段可以定义多个检查约束而且对同一个字段可以同时定义检查约束和非空约束
例如
create table person
(
p_id int
p_name varchar()
p_age int check(p_age > ) 检查约束
constraint p_check check(p_age > ) 检查约束
);
create table person
(
p_id int
p_name varchar()
p_age int constraint p_check check(p_age > ) 检查约束
constraint p_check check(p_age > ) 检查约束
);
增加约束
如果增加UNIQUEPRIMARY KEYFOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句;
如果增加NOT NULL约束那么必须使用ALTER TABLE语句的MODIFY子句如
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type (column)
ALTER TABLE table_name MODIFY column
[CONSTRAINT constraint_name] NOT NULL;
添加外键约束
alter table emp add foreign key (deptno) references dept(deptno);
alter table emp add constraint d_fk foreign key (deptno) references dept(deptno);
添加主键约束
alter table table_name add primary key(column_name);
alter table table_name add constraint t_pk primary key(column_name);
添加非空约束
alter table person modify p_name not null;
alter table person modify p_name constraint t_notnull not null;
约束延迟(在事务提交时再验证默认为不延迟)
deferrable 延迟验证
not deferrable 不延迟验证(默认)
单独deferrable的含义就是是否允许约束检查延后进行单独设置deferrable为deferrable之后约束检查延后是不可以直接使用的要配合deferred参数如果该参数是immediate那么约束还是在DML的时候进行应用如果deferred参数设置为deferred约束就是在事务commit提交的时候应用出现错误就连带回滚rollback整个事务
创建约束时可指定开启延迟
create table tt
(
id int primary key deferrable initially deferred
);
set constraints 约束名 immediate; 关闭约束延迟
set constraints 约束名 deferred; 开启约束延迟
也可以在session级设置
alter session set constraints=immediate;
alter session set constraints=deferred;
重命名约束
alter table 表名 rename constraint 原约束名 to 新约束名
删除约束
alter table 表名 drop constraint 约束名;
alter table 表名 drop constraint 约束名 cascade; &#;删除主键时级联删除子表的外键约束
约束失效
alter table 表名 disable constraint 约束名
alter table 表名 disable constraint 约束名cascade; 失效主键时级联删除子表的外键约束
约束生效
alter table 表名 enable constraint 约束名
novalidate: 不验证老数据
validate验证老数据
控制现有数据是否应用约束例如
alter table t deferrable enable novalidate constraint SYS_C; 约束生效不验证已有数据是否符合约束条件
alter table t enable validate constraint SYS_C; 约束生效同时验证已有数据是否符合约束条件
alter table t disable validate constraint SYS_C;
约束失效同时验证已有数据是否符合约束条件之后不能对该表进行dml操作否则会报ORA错误
alter table t disable novalidate constraint SYS_C;
约束失效不验证已有数据是否符合约束条件(默认)
注约束生效(enable)后的操作都受约束的限制
查询约束信息
视图名字 描述
all_constraints 包含所有的约束基本描述信息包括约束的名字类型状态延迟性等信息
dba_constraints
user_constraints
all_cons_columns 包含定义课约束的字段信息利用这个视图可以查看约束定义在哪些字段上
dba_cons_columns
user_cons_columns
