正文

Oracle表约束

IP云
IP云V管理员/4 阅读
11/29
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

约束的概述：

约束是在表中定义的用于维护数据库完整性的一些规则

（1）.主键约束

不能为空也不能重复

在一个表中只能定义一个主键约束

Oracle会在主键上建立一个唯一索引，可以指定唯一索引的存储位置和存储参数

主键约束列值占用的空间不能超过一个数据块的大小

在主键约束的列上不能定义唯一约束

主键约束可以是列级约束，也可以是表级约束

（2）.唯一性约束

列的取值必须唯一

Oracle自动在唯一性索引上建立一个唯一性索引，可以为该唯一性索引指定存储位置与参数

在唯一性约束的列上不能定义主键约束

唯一性约束可以是列级约束，也可以是表级约束

（3）.检查约束

检查约束是用来限制列值允许的取值范围的，其表达式中必须引用相应列，并且表达式的计算结果必须是一个布尔值。

一个列可以定义多个检查约束

检查约束可以是列级约束，也可以是表级约束

（4）.外键索引

定义外键约束的列的取值或者来源于参照表的参照咧，或者为空

定义外键约束的列只能参照主表中的主键约束或者是唯一约束列

可以在一列或者是多列组合上定义外键

外键约束所在表与参照变必须在同一个数据库中

外键约束可以是列级约束，也可以是表级约束

（5）.非空约束

在同一个表中可以定义多个非空约束

非空约束不能定义在对象数据类型列上

非空约束只能是列级约束

2.约束的定义方式

（1）列级的约束

column_definition[CONSTRAINT constraint_name] constraint_type

（2）表级约束

column_definition[CONSTRAINT constraint_name] constraint_type

3.约束的状态

约束的状态分为激活状态（ENABLE）和禁用状态(DISABLE)状态两种。

当处于激活状态对数据的插入等进行检查，与约束冲突的操作将被回滚。

根据是否对表已有数据进行检查，约束状态分为验证状态与非验证状态两种。

创建与维护约束 

SQL> create table jobs(

  2  job_id varchar2(10) primary key,

  3  job_title varchar2(35) not null unique

  4  using index tablespace tbs1 storage(initial 64K next 64K),

  5  min_salary number(6),

  6  max_salary number(6)

  7  )

  8  tablespace users;


SQL> create table job_history

  2  (

  3  employee_id  number(6) references employees(employee_id),

  4  start_date date not null,

  5  end_date date not null,

  6  job_id varchar2(10) not null references jobs(job_id),

  7  department_id number(4) references departments(departments_id),

  8  constraint jh_dck check(end_date>start_date),

  9  constraint jh_pk primary key(employee_id,start_date)

 10  )

 11  tablespace users;

表已创建。

可以使用 on delete cascade 和on delete set null来删除

3.添加、修改、删除约束

添加约束 

SQL> create table books

  2  (

  3  id number(6),

  4  title varchar2(10),

  5  isbn number(20),

  6  author varchar2(50),

  7  price number(6,2),

  8  describe varchar2(1000),

  9  pid number

 10  );

表已创建。

SQL> alter table books add constraint books_pk primary key (id);

表已更改。

SQL> alter table books add constraint book_uk unique(isbn);

表已更改。

SQL> alter table books add constraint book_ck check(price between 0 and 200);

表已更改。

SQL> create table publish (

  2  pid number primary key,

  3  name char(20))l

  4  ;

name char(20))l

              *

第 3 行出现错误:

ORA-00922: 选项缺失或无效

SQL> create table publish (

  2  pid number primary key,

  3  name char(20))l

  4

SQL> create table publish (

  2  pid number primary key,

  3  name char(20));

表已创建。

SQL> alter table publish add constraint book_fk foreign key(pid) references publ

ish(pid);

表已更改。

SQL> alter table books modify title not null;

修改约束 

SQL> alter table books modify constraint book_ck validate;

表已更改。

SQL> alter table books modify primary key enable novalidate;

表已更改。

重命名约束 

SQL> alter table books rename constraint books_pk to books_pkey;

表已更改。

删出约束

（1） 通过名称删除约束 

SQL> alter table books drop constraint book_ck;

表已更改。

（2）通过制定类型删除主键约束或唯一索引 

SQL> alter table books drop unique(isbn);

表已更改。

（3）删除主键约束或唯一性约束时保留唯一性索引 

SQL> alter table books drop constraint book_uk keep index;

表已更改。

（4）删除主键约束或唯一性约束的同时删除字表中相应的外键约束 

SQL> alter table publish drop primary key;

alter table publish drop primary key

*

第 1 行出现错误:

ORA-02273: 此唯一/主键已被某些外键引用

SQL> alter table publish drop primary key cascade;

4.禁用与激活约束

通常表中的约束应该处于激活的状态，但对一些操作，由于性能方面的需要暂时将约束设置为禁用状态。

利用sql*loader从外部数据源提取大量数据到数据库中

进行数据库数据大量的导入导出操作

针对表执行一项包含大量数据操作的批处理工作

1.禁用约束 

SQL> alter table student disable unique(sname);

表已更改。

SQL> alter table student disable constraint s_pk;

表已更改。

禁用时保留其唯一性索引

（2）.激活约束 

SQL> alter table student enable constraint s_pk;

表已更改。

SQL> alter table student modify constraint s_pk enable;

5.验证约束状态

6.确定违反约束限制的记录

7,延迟约束检查

（1）创建具有引用关系的两个表，其中外键是可延迟的。 

SQL> create table new_department(

  2  deptno number primary key,

  3  dname char(10) unique);

表已创建。

SQL> create table new_employee(

  2  empno number primary key,

  3  ename char(10),

  4  deptno number constraint ne_fk references new_department(deptno)deferrable)

;

表已创建。

因为外键的完整性约束

SQL> insert into new_employee values(1,'zhang',10);
insert into new_employee values(1,'zhang',10)
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (EHR.NE_FK) - 未找到父项关键字

（2）延迟new——employee的外键检查 

SQL> alter table new_employee modify constraint ne_fk initially deferred;

表已更改。

这样就可以先向new_employee插入数据，在插入主表数据。

（3）将约束检查恢复 

SQL> alter table new_employee modify constraint ne_fk initially immediate;

表已更改。

相关阅读

目录[+]