oracleday13(维护数据完整性 约束 序列)

1、维护数据的完整性

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

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

not null (非空)用于指定某列的值 不可以是null 

举例: create table user (id number,name varchar(32) not null);

insert into user (id,name) values (1,null) 出错违反规则

unique(唯一) 用于指定某列的值 不能重复 但是可以为null

create table user(id number , name varchar2(32) unique);

insert user(id,name) values(2,'abc');

insert user(id,name) values(3,'abc'); 出错  

insert user(id,name) values(3,'null');

insert user(id,name) values(3,'null');

insert user(id,name) values(3,'null');可以 为空而且可以是多行为空 

primary key(主键)  用于唯一的标示表行的数据 当定义主键约束后 该列不但不能重复而且不能为null

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

insert into user values(1,'abc');

insert into user values(1,'abc'); 报错

insert into user values(null,'abc'); 报错

primary key 和 unique的区别 

1、一个表可以有多个 unique 但是只能有一个主键

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

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

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

foreign key(外键)

用于定义主表和从表之间的关系 外键约束要定义在从表上 主表则必须具有主键约束或是unique约束当定义外键约束后 

要求外键列数据必须在主表的主键列存在或是为null

create table class (id number primary key,name varchar2(32) ) 先建这个表 才能引用

create table student(id number primary key, name varchar2(32) not null, classId number references class(id));

check(检查)

用于强制行数据必须满足的条件

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

不满足条件就报错 无法插入

建表 商品goods  客户 customer   购买 purchase

每个表的主外键

客户的姓名不能为空值

单价必须大于0 购买数量必须在1到30之间

电邮不能重复

客户的性别默认为男 必须是男或者女

create table goods(goodsId number primary key,goodsName varchar2(36) ,unitprice number check (unitprice>0) , category varchar2(64) ,provider varchar2(64) );

create table customer (customerId number primary key,name varchar2(32) not null ,

 address varchar2(64) , email varchar2(64) unique , sex char(2)  default '男' check (sex in ('男','女') ), cardId varchar2(20));

create table purchase( customerId number references customer(customerId), goodsId number refernces goods(goodsId),  

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

如果在建表时没有建立必要的约束 则可以在建表后使用alter table 命令为表增加约束  

注意:增加not null 约束 需要使用modify 选项 而其他四种约束使用 add 选项

增加商品名不为空

alter table 表名 modify 字段名 not null;

alter table goods modify goodsName not null;

alter table 表名 add constranint 约束名 约束种类[check/unique/primary key/foreign key] (字段); 

增加身份证不重复

alter table customer add constraint uni_id unique(cardId); (constraint 约束 限制)

alter table customer add constraint ch_address check(address i n ('海定','呵呵')); 

删除约束

alter table 表名 drop constraint 约束名称 

有时候不让删主键 因为有其他的表指向主键

约束名:一个表的每一个约束 都对应一个名称

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

alter table 表名 drop constraint primary key;

不能直接删的是 alter table 表名 drop constraint unique ; 因为不知道哪个unique  其他同理

如果有外键指向该主键的某个值 则不能删除成功 需要使用 cascade (级联)

alter table 表名 drop constraint primary key cascade; 级联删除  明确告诉数据库 不要管外键 直接删除

维护数据的完整性 ——维护

列级定义  在定义表的时候同时在列后定义约束 create table user (id number primary key );

列级定义是在定义列的同时定义约束

表级定义 把各个列都定义完毕后再分别说明约束条件 在分别说明约束 如 create table user(id number,name varchar2(40), // 列定义完后的点再定义

constraint pk_id primary key id)

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

一般情况下 使用列级定义即可 但是如果遇到定义复合主键(两列一起作为主键)

create table user(iduser number,idgoods number,nums number,constraint pk_user_goods primary key(iduser,idgoods)) 

主键 变动需求要小 一般都创一个没什么变化的  主键是确定这一列的唯一标识 如果存在变化可能就不稳定

复合主键 有可能有商业逻辑的概念 如果有商业逻辑的话 要变动主键 伤筋动骨 对表的稳定性不好

不推荐使用复合主键

2、序列(sequence)

oracle 中是通过使用序列(sequence) 来处理自动增长列  希望当我们添加一条记录的时候 该列能够自动的增长

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

2、由用户创建数据库对象 并由多个用户共享

如果system 使用scott的序列 从什么增长 ? 接着增长 

3、一般用于主键或唯一列

4、可以使用序列名.currval 来看当前序列到多少号了  .nextval 增加sequence的值 然后返回sequence的值

5、是么时候使用 sequence 

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

insert 语句的子查询

insert 语句的values语句中 //最常使用

update 的set 中

update 表名 列值 = 序列名.nextval where ...;

create sequence myseq start with 1 increment by 1 //表示一次增长的步长

maxvalue 99 minvalue 1 cycle //cycle 表示当序列增加到99后再重新开始 如果不希望重新开始就nocycle

nocache //nocache 表示不缓存  

cache[10] 表示一次产生10个号供你使用 提高效率 使用缓存产生号 优点是提高效率 缺点是可能产生跳号  比如系统shut down 就可能跳号 缓存的大小就没了 直接从11开始

所以为了避免就用nocache

使用sequence 

创建一张表 

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

insert into test values (myseq.nextval,'aaa');

insert into test values (myseq.nextval,'bbb');

说明 ：myseq  表示序列名字 nextval 是个关键字

select scott.myseq.currval from dual ; 可以查看当前序列到多少号

insert into user (id,name) select my_seq.nextval,ename;  将emp下一个人插入 user表中

currval 总是返回当前sequence 的值 但要在第一个nextval 初始化后 也就是必须先使用序列.nextval    

第一个nextval 后 会自动增加定义的 increment by值 然后返回增加后的值

3、管理索引

4、管理权限的角色(dba)