# 数据完整性
***
- 数据存放在表中
- 数据完整性的问题大多是由于设计引起的
- 创建表的时候,就应当保证以后数据输入是正确的
- --错误的数据、不符合要求的数据不允许输入
--四种完整性--
- --实体完整性
- 如:有类同的学号
- --域完整性
- 如:超出设计的范围,身份证号19位
- --引用完整性
- 如:多表之间的协调 --外键约束
- --自定义完整性
- 如:自己定义
***
# 约束:对数据的限制条件:数据类型
--常见的6个约束:
- 检查约束(check) name>4
- 唯一约束(Unique) id:1 2 3 4 null
- 主键约束(Primary Key) 类似唯一约束
- 外键约束(Foreign Key) 两张表 学生表 课程表(1 2 3)
- 非空约束(Not null) 不能为null
- 默认约束(Default) address: 西安
insert into student(id,name) values(1,'zs');
# 主键和唯一约束的区别:
- a.主键不能为Null,唯一约束可以为null
- b.主键可以是复合主键,也可以是单值主键(id)
- c.一张表中,只能有一个主键(复合主键),但唯一键可以设置多次
# 约束的目的:确保表中数据的完整型
***常用的4个约束类型***
- 主键约束(Primary Key Constraint) 要求主键数据唯一,并不允许为空 PK_约束字段
- 唯一约束(Unique Constraint) 要求该列唯一,允许为空,但只能出现一个空值 UQ_约束字段
- 检查约束(Check Constraint) 某列取值范围、格式限制等,如有关年龄的约束 CK_约束字段
- 外键约束(Foreign Key Constraint) 用于两表建立关系,需要指定引用主表那列 FK_约束字段
# 约束方法:唯一约束、主键约束、序列
分类:
1.列级约束(作用一个列上,列的后面):可以有多个约束(通过空格来区别约束)
在定义表格的某一列时,在列的后面添加约束
--简单写法 create table user1( id number primary key, --添加主键 name varchar2(10) ) insert into user1 values(1,'a'); insert into user1 values(1,'b'); --报错
--完整写法 --不常用
create table user2( id number constraint woyebuzhidao primary key --添加主键 name varchar2(10) ) insert into user2 values(1,'a'); insert into user2 values(1,'b'); --报错
--添加列约束--
create table student( stuno number(3) constraint PK_stuno primary key, stuname varchar2(10) constraint NN_stuname not null constraint UQ_stuname unique, stuaddress varchar2(20) default '陕西西安' constraint CK_stuaddress check (length(stuaddress)>2), subid number(3) );
--注意:
a.报错:违反唯一约束条件 可能主键报错 也可能唯一约束报错
b.如果有多个约束,default必须放在第一位
c.check约束:如何编写?-->和使用where完全相同
d.唯一约束:可以是Null,但不适用于Null
# 约束命名规范:
约束类型_字段名
主键:PK_stuno
检查约束:CK_字段名
唯一约束:UQ_字段名
非空约束:NN_字段名
外键约束:FK_子表_父表
默认约束:一般不需要命名
--id number 长度5,主键
--name varchar2 长度10,唯一
--age number 长度3,非空
create table user3( id number(5) primary key, name varchar2(10) constraint UQ_user3_name unique, age number(3) constraint NN_user3_age not null ); insert into user3 values(1,'a',21); insert into user3 values(2,'a',21);--Error insert into user3 values(3,'a');--Error
--user4,工资的列,工资低于2000的不让加
create table user4( id number primary key, salary number constraint CK_user4_salary check(salaey >2000) ) insert into user4 values(1,1000);--Error
create table user5( id number(5) primary key, name varchar2(10) unique, age number(5) check(salary>2000), sex char(2) check(sex='男' or sex='女') );
---表级约束(了解)---
create table user6( id number, name varchar2(10), info varchar2(100), --非空只有列级约束,没有表级的 constraint PK_id primary key(id), constraint UQ_name unique(name) ) insert into user6 values(2,'b','aaa');
2.表级约束:作用于一个列/多列
在定义表格的所有列之后,再选择某些列添加约束***
--先表后约束:当表先创建完成,再通过修改表的方式添加约束
create table user7( id number, name varchar2(10), age number ) insert into user7 values(1,'a',21);--不准确 delete from user7;--清空表 alter table user7 add constraint PK_user7_id primary key(id);
--删除约束:通过修改表的方式
alter table user7 drop constraint PK_user7_id;
--外键约束【重点】--
***
本表某列的取值,完全取决于另一个表的主键
本表叫做“子表”,另一个表叫做“主表”
外键:A表中的a列――>B表中的b列 a中的数据必须来自b
create table sub( sid number(3) unique, --主键或唯一 sname varchar2(10) ); insert into sub values(1,'java'); insert into sub values(2,'python'); commit; create table student3( stuno number(3), stuname varchar2(10), stuaddress varchar2(20), stuid number(3), constraint FK_student3_sub foreign key(stuid) references sub(sid) ) insert into student3(stuno,stuname,stuid) values(1,'zs',1); insert into student3(stuno,stuname,stuid) values(2,'ls',1); insert into student3(stuno,stuname,stuid) values(3,'ww',2); commit;
--插入错误数据 insert into student3(stuno,stuname,stuid) values(4,'zl',3); --Error
delete from sub where sid =2; --Error 父表不可以删除,有对应的子表
--如果想要删除父表中的外键所指的列:级联删除/级联置空
1.级联删除
drop table student3; create table student3( stuno number(3), stuname varchar2(10), stuaddress varchar2(20), stuid number(3), constraint FK_student3_sub foreign key(stuid) references sub(sid) on delete cascade delete from sub where sid =2; --成功
2.级联置空
drop table student3; create table student3( stuno number(3), stuname varchar2(10), stuaddress varchar2(20), stuid number(3), constraint FK_student3_sub foreign key(stuid) references sub(sid) on delete set null ) insert into student3(stuno,stuname,stuid) values(1,'zs',1); insert into student3(stuno,stuname,stuid) values(2,'ls',1);
--级联删除:当删除父表中的数据时,子表会跟着删除相对应的数据
--级联置空:当删除父表中的数据时,子表会将 相对应的 那一字段的值设置为Null,其他不影响
--外键使用建议:
1.当父表没有相对应的数据时,不要向子表增加数据(如果sub表没有编号为2的课程,那么子表student不要去选择2号课程)
2.不要更改父表的数据,导致子表孤立
3.建议:在创建外键时 直接设置成 级联删除/级联置空
4.删除表?先删除子表,再删除父表
--追加约束:在创建表忘了加约束,后续可以追加约束
1.唯一、主键、检查、外键约束
alter table 表名 add constraint 约束名 约束类型
create table student4( stuno number(3), stuname varchar2(10), stuaddress varchar2(20), subid number(3) ) alter table student4 add constraint UQ_stuaddress4 unique(stuaddress); alter table student4 add constraint PK_stuno4 primary key(stuno); alter table student4 add constraint CK_stuname4 check(length(stuname)>2); alter table student4 add constraint FK_student4_sub foreign key(subid) references sub(sid);
--不适应用于 默认、非空 Error
alter table studnet4 add constraint NN_stuname not null(stuname); alter table studnet4 add constraint DF_stuname default 'hello';
--非空
alter table 表名 modify 字段名 constraint 约束名 约束类型
alter table student4 modify stuname constraint NN_stuname4 not null; --默认(建议默认约束不起名字,不写 constraint ) alter table student4 modify stuname default '没有名字';
--删除约束:两类语法
1.唯一、主键、检查、外键约束、非空:
alter table 表名 drop constraint 约束名;
特殊情况:(默认约束删除本质:就是将默认约束置空) ――>alter table student4 modify stuname default null;
2.完整性约束:保证数据的正确性、相容性、防止数据冗余等。
域完整性:列。数据类型,非空约束,检查约束,外键约束
实体完整性:行。主键约束、唯一约束
引用完整性:不同表之间。外键
自定义完整性:触发器(当执行换一个操作,会自动触发另一个操作) 例如:自定义需求 学生的上学时间 必须在出生之后
***
经典实例:用户与订单
***
create table user8( id number primary key, uname varchar2(10) not null ) create table order8( id number primary key, info varchar2(100) not null, userid number references user8(id) --userid的取值,来自于user8表的id列 ) insert into user8 values(1,'张三') insert into order8 values(2,'李四') insert into order8 values(1001,'买了个表',1) insert into order8 values(1002,'买了佛冷',2) insert into order8 values(1003,'爱的魔力转圈圈',1) insert into order8 values(1003,'炒面',666) --不是瞎取的,必须是主表的id commit;
--外键之后的删除 --删除关联后的数据 delete from user8 where id = 1 --Error---不能干掉 ,我有孩子 --在删除主表中数据时,需要先删除从表中参考的数据或置空 --不推荐删除 delete from user8 where userid =1 --先删除张山的所有订单信息 delete from user8 where id = 1 --再删除张三这个人 ***置空(实际项目推荐)*** update order8 set userid = null where userid = 1 delete from user8 where id = 1
--删除关联表:在删除主表时,必须先删除从表,或取消从表的关联关系
drop table user8 --Error 被外键引用 -------------------------------- drop table order8 drop table user8
--在删除主表时,取消外键(推荐)
drop table user8 cascade constraint
--级联置空
--删除主表中被参考数据时,从表外键字段数据自动设置为null
--关键字:on delete set null
create table user8( id number primary key, uname varchar2(10) not null ) create table order8( id number primary key, info varchar2(100) not null, userid number references user8(id) on delete set null --删除主表数据,从表自动为空 ) insert into user8 values(1,'张三') insert into order8 values(2,'李四') insert into order8 values(1001,'买了个表',1) insert into order8 values(1002,'买了佛冷',2) insert into order8 values(1003,'爱的魔力转圈圈',1) delete from user8 where id =1;--可以删除,删除主表数据,从表自动为空
--级联删除 on delete cascade
--联合主键:多个字段共同完成主键的功能
create table user9( id number, name varchar2(10), age number, constraint PK_u9 he primary key(id,name) ) insert into user9 values(1,'a',21); insert into user9 values(1,'b',21); insert into user9 values(2,'a',21); insert into user9 values(1,'b',29);--Error
--commit 要把在内存的数据,添加到数据库文件.dbf