数据完整性约束是在表和字段上强制执行的数据检验规则,为了防止不规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,主要是对空值和重复值的约束,使不符合规范的数据不能进入数据库,以保证数据存储的完整性和准确性。
完整性分类
按照约束的不同类型可以分为实体完整性、域完整性、参照完整性、用户自定义完整性。
实体完整性
用来标识表中的每一条记录,一条记录代表一个实体(entity)。
主键约束(primary key)每个表中只能有一个主键。
主键值须非空不重复。
可设置单字段主键,也可设置多字段联合主键。
联合主键中多个字段的数据完全相同时,才违反主键约束。
列级添加主键约束:
creat table (
primary key,
,
……
);
示例:
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
表级添加主键约束:
create table (
,
,
......
,
[constraint 主键约束名] primary key(字段名1[,字段名2,...字段名n]));
示例:
mysql> create table employee(
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int,
-> primary key(e_name,e_sex));
已有表添加主键约束:
alter table add [constraint 主键约束名] primary key(字段名);
示例:
-- 创建无主键约束的表
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加主键约束
mysql> alter table employee add primary key(e_id);
删除主键约束:
alter table drop primary key;
示例:
alter table employee drop primary key;
唯一约束(unique)指定列的数据不能重复。
可以为空,但只能出现一个空值。
列级添加唯一约束:
creat table (
unique,
,
……
);
示例:
mysql> create table employee(
-> e_id int,
-> e_name varchar(5) unique,
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
表级添加唯一约束:
creat table (
,
,
……
,
[constraint 唯一约束名] unique (字段名1[,字段名2...字段名n]));
示例:
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int,
-> unique (e_name,e_sex));
已有表添加唯一约束:
alter table add [constraint 唯一约束名] unique (字段名);
示例:
-- 创建表
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加唯一约束
mysql> alter table employee add unique(e_name);
删除唯一约束:
alter table drop index ;
如果单个字段没有指定唯一约束名,则默认的唯一约束名为字段名。如果是多个字段组合为唯一约束时候,默认的唯一约束名为第一个字段的名称。如果指定了约束名,则删除的时候写约束名。
示例:
alter table employee drop index e_name;
自动增长列(auto_increment)指定字段的数据自动增长。
配合主键一起使用,并且只适用于整数类型。
默认从1开始,每增加一条记录,该字段的值会增加1。
即使数据删除,还是从删除的序号继续增长。
创建自动增长约束:
creat table (
primary key auto_increment,
,
……
);
示例:
mysql> create table employee(
-> e_id int primary key auto_increment,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
已有表添加自动增长约束:
alter table modify auto_increment;
示例:
-- 创建无自增约束的表
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加自增约束
alter table employee modify e_id int auto_increment;
设置自动增长初始值:
alter table auto_increment=初始值;
示例:
alter table employee auto_increment=1000;
删除自增约束:
alter table modify ;
示例:
alter table employee modify e_id int;
域完整性
域完整性是针对单元格的约束,不与其它行参考比较。
非空约束(not null)字段的值不能为空。
创建非空约束:
creat table (
not null,
,
……
);
示例:
mysql> create table employee(
-> e_id int,
-> e_name varchar(5) not null,
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
已有表添加非空约束:
alter table modify not null;
示例:
-- 创建表
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加非空约束
alter table employee modify e_name int not null;
删除非空约束:
alter table modify [null];
示例:
alter table employee modify e_name int;
默认约束(default)如果新插入一条记录时没有为该字段赋值,系统会自动为这个字段赋值为默认约束设定的值。
如果插入的数据为“null”,则不会使用默认值,只有没有插入数据时候,才会使用默认值。
创建默认约束:
creat table (
default value,
,
……
);
示例:
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int default 0,
-> d_id int);
已有表添加默认约束:
alter table modify default value;
示例:
-- 创建表
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加默认约束
alter table employee modify e_age int default 0;
删除默认约束:
alter table modify ;
示例:
alter table employee modify e_age int;
参照完整性
参照完整性指多表之间的对应关系,在一张表中执行数据插入、更新、删除等操作时,DBMS都会跟另一张表进行对照,避免不规范的操作,以确保数据存储的完整性。
外键约束(foreign key)
某一表中某字段的值依赖于另一张表中某字段的值
主键所在的表为主表,外键所在的表为从表
每一个外键值必须与另一个表中的主键值相对应
创建外键约束:
creat table (
,
,
……
,
[constraint 外键约束名] foreign key(字段名) references (主键字段));
示例:
-- 创建一个主表
mysql> create table department(
-> d_id int primary key,
-> d_name varchar(5),
-> d_num int);
-- 创建从表的同时添加外键
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int,
-> foreign key(d_id) references department(d_id));
已有表中添加外键约束:
alter table add [constraint 外键约束名] foreign key(字段名) references (主键字段);
示例:
-- 创建一个主表
mysql> create table department(
-> d_id int primary key,
-> d_name varchar(5),
-> d_num int);
-- 创建从表
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加外键约束
mysql> alter table employee add constraint fk_d_id foreign key(d_id) references department(d_id));
删除外键约束:
alter table drop foreign key ;
示例:
alter table employee drop foreign key fk_d_id;先删除从表再删除主表。
先删除外键约束,再删除表。
用户自定义完整性
根据用户实际的完整性要求来定义,在执行数据插入、更新等操作时,DBMS会检查数据是否满足检查约束中的限定条件,避免不符合条件的操作,以保证数据存储的准确性。
检查约束(check)指定需要检查的限定条件。
创建检查约束:
creat table (
,
,
……
,
check());
示例:
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int,
-> check(e_age>=0));
已有表添加检查约束:
alter table add constraint check();
示例:
-- 创建表
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加检查约束
mysql> alter table employee add constraint ch_e_age check(e_age>=0));
删除检查约束:
alter table drop constraint ;
示例:
mysql> alter table employee drop constraint ch_e_age;