oracle维护数据的完整性

数据的完整性用于确保数据库数据遵从一定的商业的逻辑规则。在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

n  约束

约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括：not null（非空）、unique（唯一），primary key（主键），foreign key（外键）和check（检查）五种。

 

①not null（非空）

用于指定某列的值，不可以是null

SQL> create table user1(id number,name varchar2(32) not null);

SQL> insert into user1 values(1,null);

ORA-01400: 无法将 NULL 插入 ("SCOTT"."USER1"."NAME")

②unique（唯一）

用于指定某列的值不能重复，但可以为空，而且允许多行为空。

③primary key（主键）

用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。

☞primary key 和unique 区别

1.一个表可以有多个unique，但是只能有一个primary key

2.我们每张表都应该有一个主键

3.unique列的值可以为null，但是主键不可以为空

4.primary key的所在列，会自动创建索引，但是unique不会自动创建索引。

 

④foreign key（外键）

用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

SQL> create table class(id number primary key,name varchar2(32));

SQL> create table students(id number primary key,

      name varchar2(32),

      classid number references class(id));

 

⑤check（检查）

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间就会提示出错。

SQL> create table user4(id number primary key,sal number check(sal>=1000 and sal<=2000),sex char(2) check(sex in(‘男’,’女’));

 

n  商店售货系统表设计案例

现有一个商店的数据库，记录客户及其购物情况，有下面三个表组成：

商品goods（商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider）；

客户customer（客户号customerId，姓名name，住址address，电邮email，性别sex，身份证cardId）；

购买purchase（客户号customerId，商品号goodsId，购买数量nums）；

请用SQL语言完成下列功能：

1.建表，在定义中要求声明：

（1）每个表的主外键；

（2）客户的姓名不能为空值；

（3）单价必须大于0，购买数量必须在1到30之间；

（4）电邮不能够重复；

（5）客户的性别必须是男或女，默认是男。

SQL> create table goods(

  2  goodsId number primary key,

  3  goodsName varchar2(32),

  4  unitprice number check(unitprice>0),

  5  category varchar2(64),

  6  provider varchar2(64));

Table created

 

SQL> create table customer(

  2  customerId number primary key,

  3  name varchar2(32) not null,

  4  address varchar2(64),

  5  email varchar2(32) unique,

  6  sex char(2) default '男' check(sex in ('男','女')),

  7  cardId char(18));

Table created

 

SQL> create table purchase(

  2  customerId number references customer(customerId),

  3  goodsId number references goods(goodsId),

  4  nums number check(nums>=1 and nums<=30));

Table created

 

如果在建表是忘记建立必要的约束，则可以再建表后使用alter table命令为表增加约束，但是要注意：增加not null约束是需要使用modify选项，而增加其他四种约束使用add选项。

（1）每个表的主外键；

（2）客户的姓名不能为空值；—增加商品名也不能为空

（3）单价必须大于0，购买数量必须在1到30之间；

（4）电邮不能够重复；—增加身份证也不重复

（5）客户的性别必须是男或女，默认是男。

（6）增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、‘西城’、‘通州’、‘崇文’

基本语法：

alter table 表名 add constraint 约束名 约束种类（字段）

alter table 表名 modify 字段名 not null

 

 

alter table customer add constraint ch_address check(address in('海淀','朝阳','东城','西城','通州','崇文'));

SQL> alter table customer add constraint uni_id unique(cardId);

 

n  删除约束

当不再需要某个约束是，可以删除。

alter table 表名 drop constraint 约束名称

特别说明：因为一张表只能有一个主键，因此在删除主键的约束的时候，可以直接使用primary key：

alter table 表名 drop primary key；

如果在两张表存在主从关系，有外键值指向该主键的某个值，不能成功删除，在删除主表的主键约束时，必须带上cascade选项，如：

alter table 表名 drop primary key cascade；

 

n  列级定义

列级定义是指在定义表的后面直接在列后面同时定义约束，如：

create table user8(id number primary key ,name varchar2(32));

如在department表定义主键约束

create table department

(dept_id number(2) constraint pk_department primary key,

name varchar2(12),loc varchar(12));

 

n  表级定义

表级定义是指在定义了所有列后，再定义约束，这里需要注意：not null 约束只能在列级上定义

 

create table employee(

emp_id number(4),

name varchar2(32),

dept_id number(2),

constraint pk_employee primary key(emp_ id),

constraint fk_department foreign key(dept_id) references department(dept_id));

 

 

语法：在最后一个字段,后，定义约束

constraint 约束名 primary key(字段）

constraint 约束名 foreigin key(字段）references 主表(字段)

constraint 约束名 unique(字段…）

not null(字段）

constraint 约束名 check(字段条件）

一般情况下，我们使用列级定义即可，但是如果遇到定义复合主键（两列一起定义为主键），就要使用表级定义

SQL> create table user9(iduser number,idgoods number,nums number,

  2  constraint pk_user_goods primary key(iduser,idgoods))

  3  ;

不推存大家使用复合主键

 

 

n  序列（SEQUENCE）

一个问题：在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始，自动的增长，怎么处理？

在sql server和mysql中都是可以在定义表的时候，直接给指定自增长

Sql server：

Create table temp(

Id int primary key identity(1,1),

Name varchar(36));

 

Mysql:

Create table temp(

Id intprimary key auto_incrment,

Name varchar(36));

 

在oracle中，是通过使用序列（sequence）来处理自动增长列。

（1）可以为表中的列自动产生值

（2）由用户创建数据库对象，并可有多个用户共享

如system可以使用scott创建的序列

问题：如果system使用scott的序列，从什么开始增长？答：接着增长。不是重新从1开始增长。

（3）一般用于主键或唯一列

（4）可以使用序列名.currval返回当前序列的值到多少号

 

create sequence my_seq                         ---创建序列名

start with 1                                      ---从1开始

increment by 1                                   ---每次增长1，步长

maxvalue 3000    //nomaxvalue(不设最大值)        ---设置最大值

minvalue 1                                       ---设置最小值

cycle             // nocycle(一直累加，不循环)     ---设置循环

nocache                                         ---设置缓存

[cache 10]  ---表示一次产生10个好供你使用，使用缓存产生的号，优点是提高效率，缺点是可能产生跳号。

从1开始，每次增长1，最大值为3000，之后又循环从1开始

SQL> create sequence my_seq

  2  start with 1

  3  increment by 1

  4  maxvalue 3000

  5  minvalue 1

  6  cycle

  7  nocache

  8  ;

Sequence created

 

使用：创建一张表

 

SQL> create table test1(id number primary key,name varchar2(32));

 

SQL> insert into test1 values(my_seq.nextval,'abc');

1 row inserted

SQL> insert into test1 values(my_seq.nextval,'ddd');

1 row inserted

说明：my_seq：表示序列名字；

      nextval：这是一个关键字

 

细节说明

一旦定义了某个序列，你就可以用currval、nextval

currval：返回sequence的当前值

nextval：增加sequence的值，然后返回sequence值

比如：

序列名.currval

序列名.nextval

 

什么时候使用sequence：

--不包含子查询、snapshot、VIEW的select语句

--insert语句的子查询中

---insert语句的values中

---update的set中

 

n  注意事项：

①currval总是返回当前sequence的值，但是在第一次nextval初始化之后才能使用currval，否则会出错。一次nextval会增加一次sequence的值，所以如果你在同一个语句里面使用多个nextval，其值就是不一样的。

②第一次nextval返回的是初始值；随后的nextval会自动增加你定义的increment by的值，然后返回增加后的值。

③如果指定cache值，oracle就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的值取完后，oracle自动再取一组到cache，使用cache或许会跳号，比如数据库突然不正常down掉（shutdown abort），cache中的sequence就会丢失，所以可以再create sequence的时候用nocache防止这种情况发生。