mysql 域完整性_MySQL完整性约束

数据完整性约束是在表和字段上强制执行的数据检验规则，为了防止不规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，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;