数据的完整性用于确保数据库数据遵从一定的商业的逻辑规则。在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防止这种情况发生。