oracle-约束-序列
概要图
一 约束
--问题：往表中插入数据的时候，可能出现一些问题，比如：重复插入数据，内容不对（性别）
--如何保证数据库表中数据的完整性和一致性呢？
约束是强加在表上的规则或条件，确保数据库满足业务规则，保证数据的完整性。
约束又分为列约束（Column Constraint）和表约束（Table Constraint）。列约束作为列定义的一部分只作用于此列本身。表约束作为表定义的一部分，可以作用于多个列
--常见的约束：主键（primary key），外键（foreign key）,唯一（unique）,非空（not null）,默认（default）,用户自定义（check）
1.1 主键约束
--什么是主键约束？主键约束就是给表定义一个主键，什么是主键？
--主键主要是用来保证表记录的唯一非空的。
--建表的时候添加主键
create table student(stuno number(4) primary key,stuname varchar2(10),age number(2),addr varchar(50));
insert into student values(1001,'zhang',18,'luoyang');
--再次插入相同记录，违法主键约束，不允许插入
SQL> insert into student values(1001,'zhang',18,'luoyang');
insert into student values(1001,'zhang',18,'luoyang')
ORA-00001: 违反唯一约束条件 (SYS.SYS_C0010797)
--如果主键为null也不允许插入，同样是违反了主键约束
SQL> insert into student values(null,'zhang',18,'luoyang');
insert into student values(null,'zhang',18,'luoyang')
--第一种 create table student2( stuno number(5) primary key, stuname varchar2(8), age number(2), addr varchar(50) ) --第二种 create table student2( stuno number(5) constraints pk_stuno primary key, stuname varchar2(8), age number(2), addr varchar(50) ) --第三种 create table student ( stuno number(5), stuname varchar2(5), age number(5), addr varchar(50), constraints pk_stuo primary key(stuno) );
--创建一张课程表：
create table course(cno number(4) primary key,cname varchar2(20),cscore number(2));
--创建一张成绩表：学号和课程号共同作为一个主键，称为联合主键
--一张表只能有一个主键
create table score(sno number(4),cno number(4),score number(5,2)，constraint pk_score primary key (sno,cno));
drop table course;
create table course(cno number(4) constraint pk_course primary key ,cname varchar2(20),cscore number(2));
作业：
- 删除表空间时，如何删除相关联的数据文件
drop tablespace test_data including contents and datafiles;
1.2 非空约束（not null）
非空（NOT NULL）约束：顾名思义，所约束的列不能为NULL值。否则就会报错 。
举例：
create table user1(id number,name varchar2(30) not null);
insert into user1 values(001,'');//会报错
1.3 唯一约束(unique)
唯一（UNIQUE）约束：在表中每一行中所定义的这列或这些列的值都不能相同。必须保证唯一性。否则就会违反约束条件。
用于指定列的值不能重复，但可以为null。
CONSTRAINT constraint_name UNIQUE (col_name1[, col_name2,…])
举例：
create table user3(id number unique,name varchar2(30));
insert into user3 values(1,'111');
insert into user3 values(1,'111');//报错，唯一性
insert into user3 values(null,111);
例子
--创建唯一约束的三种方法 --(1) create table student( sno number(4) primary key, sname varchar2(20) not null, age number(3), addr varchar2(50), idcard number(18) unique); --(2)c create table student( sno number(4) primary key, sname varchar2(20) not null, age number(3), addr varchar2(50), idcard number(18) constraint uk_idcard unique); --（3） create table student( sno number(4) primary key, sname varchar2(20) not null, age number(3), addr varchar2(50), idcard number(18),constraint uk_idcard unique(idcard));
primary key与unique的区别：
1.一张表可以有多个unique(唯一)约束；
2.一张表只能有一个主键；
3.设置为主键的列不能有null值；
1.4 非空约束（not null）
非空（NOT NULL）约束：顾名思义，所约束的列不能为NULL值。否则就会报错 。
举例：
create table user1(id number,name varchar2(30) not null);
insert into user1 values(001,'');//会报错
1.5 检查约束(check)
条件（CHECK）约束：表中每行都要满足该约束条件。条件约束既可以在表一级定义也可以在列一级定义。在一列上可以定义任意多个条件约束。
CONSTRAINT constraint_name CHECK (logical_expression)
举例：
create table user4
(id number primary key,
sal number check(sal>=1000 and sal<=2000),
sex char(2) check(sex in('男','女')));
insert into user4 values(1,5000,'男');//sal列的值不满足1000至2000，报错。
1.6 外键约束（froeign key）
外键（FOREIGN KEY）约束：用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性。能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库；
用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必需具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必需在主表的主键列存在或是为null。
CONSTRAINT constraint_name
FOREIGN KEY （col_name1[, col_name2,…]）
REFERENCES ref_table [(ref_col1[,ref_col2,…])]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ] ]
[ NOT FOR REPLICATION ]
houzhenhua@hope-pact.com
举例：
create table class(id number primary key,name varchar2(32)); create table stus(id number primary key, name varchar2(36) not null, classid number references class(id) );
注意
1.外键指向主键列；
2.外键可以指向unique列；
3.建表时先建主表，再建从表；删除表先删从表，再删主表。
4.外键列属性值要与主键或unique列属性值的类型保持一致
5.外键列的值，必需在主键列中存在。但外键列的值允许为null
6.ON DELETE {CASCADE | NO ACTION}指定在删除表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在删除父表数据行时会将子表中对应的数据行删除；如果指定的是NO ACTION，则SQL Server 会产生一个错误，并将父表中的删除操作回滚。NO ACTION 是缺省值。
例子
create table student( sno number(4) primary key, sname varchar2(20) not null, age number(3), addr varchar2(50), idcard number(18)); create table course( cno number(4)primary key, cname varchar2(20), cscore number(2)); --创建外键约束的第一种方式 create table score( stuno number(4) references student(sno), --创建第一个外键约束 couno number(4) references course(cno), --创建第二个外键约束 score number(5,2), constraint pk_score primary key(stuno,couno)); --创建一个主键约束 --删除表及该表关联的约束 drop table score cascade constraints; --创建外键约束的第二种方式 create table score( stuno number(4) constraint fk_stuno references student(sno), --创建第一个外键约束 couno number(4) constraint fk_couno references course(cno), --创建第二个外键约束 score number(5,2), constraint pk_score primary key(stuno,couno)); --创建外键约束的第三种方式 create table score(stuno number(4), couno number(4), score number(5,2), constraint fk_stuno foreign key(stuno) references student(sno), constraint fk_couno foreign key(couno) references course(cno), constraint pk_score primary key(stuno,couno));
insert into student values(1001,'zhangsan',19,'henanluoyang',98765432123456789);
insert into student values(1002,'lisi',19,'henanluoyang',98765432123456789);
insert into student values(1003,'wangwu',19,'henanluoyang',98765432123456789);
--查看student表中所有内容
select * from student;
--往course表中插入2条记录
insert into course values(9999,'Java基础',4);
insert into course values(9998,'数据库基础'，2);
--查看course表中所有记录
select * from course;
--思考：insert into score values(1003,9997,89);能成功吗？不能，因为course表中不存在课程号为9997的课程
--思考：insert into score values(1003,9998,89);能成功吗？可以，因为1003在student表中存在，9998在course表中存在
--总结4：定义外键约束之后，删除父表中的记录，需要先将相关子表的记录删除
delete from student where sno=1003; --删除不掉，提示： 违反完整约束条件 (SYS.SYS_C0010808) - 已找到子记录
--正确的做法：
delete from score where stuno=1003; --先删子记录
delete from student where sno=1003; --后删父记录
1.7 默认值(default)
CONSTRAINT constraint_name
DEFAULT constant_expression [FOR column_name]
举例：
Create table stu( id number primary key, address varchar2(50) default ‘宿舍’); insert into stu(id) values(1);
二 约束管理
2.1 添加约束
如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束；
注意：
1）增加not null约束使用modify（因为字段（列）默认都是可以为空）
2) 添加默认值 alter table emps modify deptno default 10
2）增加其它四种约束使用add。
1.添加not null约束
alter table 表名 modify 字段名 not null;
2.添加unique(唯一)、primary key(主键)、foreign key(外键)和check(检查)约束
alter table 表名 add constraint 约束名 约束种类(字段);
示例：
--添加主键约束
alter table stuInfo
add constraint PK_stuNo primary key(stuNo)
--添加唯一键约束
alter table stuInfo
add constraint UQ_stuID unique(stuID)
--添加默认约束
alter table emps modify deptno default 10
--添加检查约束
alter table stuInfo
add constraint CK_stuAge check(stuAge between 15 and 40)
--添加外键约束
alter table stuInfo
add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
2.2 删除约束
当不再需要某个约束时，可以删除。删除约束基本语法：
alter table 表名 drop constratint 约束名称;
约束名称指的是：一个表的每一个约束都对应一个名称。约束名称用户没有设置时，系统会自动分配一个名称。
在删除主键约束的时候，可能有错误，比如：alter table 表名 drop primary key;这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必需带上cascade 选项。
基本语法：
alter table 表名 drop primary key cascade;
2.3 约束命名规范
约束名称：建议自己定义一套命名规则，否则使用系统生成的约束名，很难能把它和对应的表、字段联系起来。
约束命名规则：
唯一约束： UK_表名_列名
主键约束： PK_表名
外键约束： FK_表名_列名
条件约束： CK_表名_列名
2.4 显示约束信息
1）显示表约束信息
通过查询数据字典视图user_constraints，可以显示当前用户所有的约束的信息。
语法：
select constraint_name, constraint_type, status, validated
from user_constraints
where table_name = '表名';
注意：表名要大写
|
Type Code |
Type Description |
Acts On Level |
|
C |
Check on a table |
Column |
|
O |
Read Only on a view |
Object |
|
P |
Primary Key |
Object |
|
R |
Referential AKA Foreign Key |
Column |
|
U |
Unique Key |
Column |
|
V |
Check Option on a view |
Object |
2）当然也有更容易的方法，直接用pl/sql developer查看即可。
2.5表级约束和列级约束
n 列级约束
列级约束：是在定义列的同时定义约束。
举例：如果在department表定义主键约束
create table department(
dept_id number(12) constraint pk_department primary key,
name varchar2(12), loc varchar2(12));
n 表级约束
表级定义：指在定义了所有列后，再定义约束。
举例：建立employee表时定义主键约束和外键约束为例：
create table employee(emp_id number(4),
name varchar2(15),
dept_id number(2),
constraint pk_employee primary key (emp_id),
constraint fk_department foreign key (dept_id) references department4(dept_id)
);
特别说明：not null约束不可以出现在表级定义中，not null约束只能在列级上定义。
一般情况下，我们使用列级定义即可。但是如果遇到定义复合主键(两列一起被定义为主键)时，需要用到表级定义。
2.6 联合主键
举例，将id与name定义为复合主键：
create table test(id number,
name varchar2(64),nums number,
constraint pk_id_name primary key(id,name));
特别说明：不推荐大家使用复合主键。
三 序列
序列是什么？做什么用的？怎么用？需要注意哪些内容？
--序列是什么？有序的数字组成的一个排列 1 2 3 4 5 6 7
--做什么用？序列是一个独立的数据库对象，主要用来生成主键
--怎么用?
--创建序列： create sequence seq01 --seq01序列名 start with 3 --序列的起始值 increment by 1 --步长 maxvalue 9999 --序列的最大值 minvalue 0 --序列的最小值 nocycle --nocycle(表示序列不循环)|cycle(表示序列循环使用) cache 20; --cache 20(为了加快序列的生成速度，每次生成20个值，放到缓存中)|nocache（不往缓存中存放序列值，使用一次，生成一次） --currval:返回序列的当前值，不会引起序列自增 --nextval：返回序列的下一个值，会引起序列自增 --查看序列的下一个值，会引起序列自增 select seq01.nextval from dual;--第一次使用序列的时候，必须使用 序列名.nextval --查看序列的当前值 select seq01.currval from dual; --序列的使用： insert into student values(seq01.nextval,'zhang','男',18,'河南信阳','123456789987654321'); --注意事项：一个sequence可以供多张表使用的。可能造成主键值的不连续。 --删除序列： drop sequence seq01;
作者：8亩田
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
本文如对您有帮助，还请多帮 【推荐】 下此文。
如果喜欢我的文章，请关注我的公众号
如果有疑问,请下面留言
