Oracle约束操作(表级约束)

约束的概念：
约束是在表中定义的用于维护数据库完整性的一些规则。通过为表中的字段定义约
束，可以防止将错误的数据插入到表中。

注意：
1.如果某个约束只作用于单独的字段，既可以在字段级定义约束，也可以在表级定义约束；但如果某个约束将作用于多个字段，必须在表级定义约束。
2.oracle中的约束通过名称来进行识别。在定义约束时可以通过constraint关键字为约束命名。如果用户没有为约束指定名称，oracle将自动为约束建立默认的名称。

主键约束(primary key)：
主键约束的特点：
 定义为主键约束的字段中不能包含任何重复值，并且不能包含null值。
 同1个表中只能定义1个主键约束。
 可以为一个字段定义主键约束，也可以为多个字段的组合定义主键约束。
 oracle会自动为具有主键约束的字段建立1个唯一索引和1个非空约束。
例如：
 create table person
 (
       p_id int primary key,        --定义该字段为主键约束
       p_name varchar2(20),
       p_age int

   --constraint p_pk primary key (p1_id)

 );

create table person
(
       p_id int constraint p_pk primary key,    --定义该字段为主键约束，并指定约束名字
       p_name varchar2(20),
       p_age int

      --constraint p_pk primary key  (p1_id)
);

create table person
(
       p_id int,
       p_name varchar2(20),
       p_age int,
       constraint p_pk primary key (p_id,p_name)--定义复合主键,并指定名字

      --primary key(p_id,p_name)
);

非空约束(not null)：
非空约束的特点：
 定义了非空约束的字段中不能包含null值。
 只能在字段级定义非空约束。
 在同一个表中可以定义多个非空约束。

例如：
create table person
(
       p_id int,
       p_name varchar2(20) not null, --定义该列的非空约束
       p_age int
 );

唯一约束(unique):
唯一约束的特点：
 定义了唯一约束的字段中不能包含重复值。
 可以为1个字段定义唯一约束，也可以为多个字段的组合定义唯一约束。因此，唯一约束既可以定义在字段级，也可以定义在表级。
 oracle会自动为具有唯一约束的字段建立1个唯一索引。
 对同一字段可以同时定义非空和唯一约束。
 如果在1个字段上仅定义了唯一约束，而没有定义非空约束，则该字段可以包含多个null值。

例如：
create table person
(
       p_id int,
       p_name varchar2(20) unique,--定义该列的唯一约束
       p_age int
);

create table person
(
       p_id int,
       p_name varchar2(20) constraint p_un unique not null, --同时定义唯一和非空约束
       p_age int
);

create table person
(
       p_id int,
       p_name varchar2(20),
       p_age int,
       constraint p_un unique(p_name) --指定约束名字
);

外键约束（foreign key）
 外键约束的特点：
  定义为外键约束的字段中只能包含相应的其他表中引用字段的值或null值。
  可以为1个字段定义外键约束，也可以为多个字段的组合定义外键约束。
  定义了外键约束的字段和相应的引用字段可以存在于同1个表中，称为自引用。
  对同1个字段可以同时定义外键和非空约束。
  主表中的被引用列，必须有主键约束或唯一约束。

例如：
 create table person
 (
       p_id int,
       p_name varchar2(20),
       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 varchar2(20),
       p_age int,
       w_id int constraint p_fk references works-- 外键约束，引用works表中的主键
  );

create table person
 (
       p_id int,
       p_name varchar2(20),
       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）
 检查约束的特点：
  在检查约束的表达式中必须引用到表中的一个或多个字段，并且表达式的计算结果必须是一个布尔值。
  在表达式中不能包含子查询。
  在表达式中不能包含sysdate,uid,user,userenv等sql函数，也不能包含rowid,rownum等伪列。
  检查约束可以在字段级和表级定义。
  对同一个字段可以定义多个检查约束，而且对同一个字段可以同时定义检查约束和非空约束。

例如：
  create table person
(
       p_id int,
       p_name varchar2(20),
       p_age int check(p_age > 20)   --检查约束
    -- constraint p_check check(p_age > 20)      --检查约束
);
  create table person
(
       p_id int,
       p_name varchar2(20),
       p_age int constraint p_check check(p_age > 20)  --检查约束
     -- constraint p_check check(p_age > 20)      --检查约束
);

 

增加约束
如果增加UNIQUE、PRIMARY KEY、FOREIGN 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);

 

注：

添加foreign key约束(多字段／表级）

SQL> alter table employees

add constraint emp_jobs_fk foreign key (job,deptno)

references jobs (jobid,deptno)

on delete cascade   //指定父表删除记录时子表如何处理！

更改foreign key约束定义的引用行为(delete cascade/delete set null/delete no  action),默认是delete on action

引用行为(当主表中一条记录被删除时，确定如何处理子表中的外部码字段）：

delete cascade : 指删除父表记录时删除子表中所有的相关记录

delete set null :指删除父表记录时 将所有相关记录的外部码字段值设置为NULL

delete no action: 指删除父表记录时 不做任何操作

添加主键约束
 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 person drop primary key;

alter table person drop primary key cascade;

注：删除约束时，默认将同时删除约束所对应的索引，如果要保留索引，用KEEP INDEX关键字

SQL> alter table employees drop primary key keep index

约束失效
 alter table 表名  disable constraint 约束名

 alter table 表名  disable constraint 约束名 cascade； --失效主键时级联删除子表的外键约束
约束生效
 alter table 表名 enable constraint 约束名

novalidate: --不验证老数据
validate：--验证老数据
控制现有数据是否应用约束，例如
alter table t2 deferrable enable novalidate constraint SYS_C007211; -- 约束生效，不验证已有数据是否符合约束条件

alter table t2 enable validate constraint SYS_C007211;  -- 约束生效，同时验证已有数据是否符合约束条件

alter table t2 disable validate constraint SYS_C007211;   

--约束失效，同时验证已有数据是否符合约束条件，之后不能对该表进行dml操作 ，否则会报ORA-25128错误
 
alter table t2 disable novalidate constraint SYS_C007211;
-- 约束失效，不验证已有数据是否符合约束条件（默认）

 

注：约束生效（enable）后的操作都受约束的限制。

查询约束信息
 视图名字       描述
 all_constraints   包含所有的约束基本描述信息，包括约束的名字、类型、状态、延迟性等信息
 dba_constraints
 user_constraints

 all_cons_columns   包含定义课约束的字段信息，利用这个视图可以查看约束定义在哪些字段上
 dba_cons_columns
 user_cons_columns