【mysql】数据完整性约束

以你之姓@ 2022-12-26 11:09 112阅读 0赞

### 文章目录 ###

*  一：实体完整性
 *   *  （1）我们来看一下创建表时将某一个属性定义为主键——在对应列后面加\`primary key\`
     *  （2）创建表并将多个属性的组合定义为主键。———在表的属性列定义结束之后，写：primary key(属性名列表)
     *  （3）给约束起约束名。在约束定义前加“constraint 约束名”——格式：constraint 约束名 约束定义
     *  （4）添加主键——对已创建好的表添加主键，即对表进行修改。
     *  （5）删除约束
 *  二、唯一约束：unique，实现属性值唯一
 *   *  （1）创建表时设置唯一约束
     *  （2）删除唯一约束
 *  三、用户定义的完整性——check
 *   *  （1）创建表时添加check
     *  （2）给已有表添加check
     *  （3）删除check，和删除主键约束一样
 *  三、参照完整性——外键（码）
 *   *  （1）给已有表添加外码：该属性不是该表的码，是另一个表的码
     *  （2）建表时创建外码约束
     *   *  a) 表级形式
         *  b)列级约束

# 一：实体完整性 #

首先我们来看一下实体完整性

首先我们执行下面**sql** 语句

CREATE TABLE test1(
    	n1 INT,
    	n2 INT,
    	n3 INT)

执行下面**sql**语句三遍

INSERT INTO test1
    VALUES(1,2,3)

我们会发现，数据表中出现了三条一样的数据  
![在这里插入图片描述][20201204203442318.png]  
但是，在大多数情况下，我们要在表的每一行有一个唯一标识，使得每一行中有一个或多个属性字段值不相同，所以我们引入主键（码） `primary` `key`，来实现实体完整性，将字段加上主键，那么这个字段必须是唯一而且非空的。

## （1）我们来看一下创建表时将某一个属性定义为主键——在对应列后面加`primary key` ##

可以采用列级形式定义约束，只能访问一个属性，对于主键约束来说，只能将单个属性定义为主键。  
下面我们来看一个例子：  
**例：**

create table test2
    	(	n1 int primary key,
    		n2 int,
    		n3 int
    	)

我们在来插入数据

INSERT INTO test2
    VALUES(1,2,3);

我们发现在执行第一条的时候成功了，但是执行第二条的时候却失败了，提示  
`Duplicate entry '1' for key 'PRIMARY'`  
证明主键中已经有了一个相同的值，插入失败

## （2）创建表并将多个属性的组合定义为主键。———在表的属性列定义结束之后，写：primary key(属性名列表) ##

在有些情况下，我们设置一个属性字段为主键实现不了相关功能，必须让多个属性字段合起来是唯一的，那么我们就需要设置多个属性字段为主键。

我们先来一个**错误**的示范

例2：错误写法。

create table test3
    	(	n1 int primary key,
    		n2 int primary key,
    		n3 int
    	)

这样的做法是错误的，我们会发现当我们执行这条sql语句时，报错：`Multiple primary key defined`，提示我们定义了多个主键

**正确方法：** 采用约束的表级形式，可以访问多个属性，对于主键约束来说，可以定义单个属性或多个属性的组合为主键。  
下面我们来看下**正确**的例子：

格式：在表的属性列定义结束之后，写：primary key(属性名列表)  
例3：

create table test3
    	(	n1 int,
    		n2 int,
    		n3 int,
    		primary key(n1,n2)
    	);

insert into test3
    	values(1,2,3);

当我们执行第二次插入的时候，我们发现插入失败`Duplicate entry '1-2' for key 'PRIMARY'` 出错,主键约束起作用。

insert into test3
    values(1,1,3);

我们发现插入成功了。  
这里我们要**注意**：n1不是主键，(n1,n2)是主键。

## （3）给约束起约束名。在约束定义前加“constraint 约束名”——格式：constraint 约束名 约束定义 ##

create table test4
    (	n1 int,
    	n2 int,
    	n3 int,
    	constraint  pk_test4 primary key(n1)
    )

约束的列级形式也可以定义约束的名字，方法和表级约束形式一样，写在对应列定义之后。  
以上定义主键的形式是在**创建表时创建**。  
下面我们来看一下当我们创建表以后，但是我们却没有在表中加入主键时该怎么办。

## （4）添加主键——对已创建好的表添加主键，即对表进行修改。 ##

**格式**：

alter table 表名
    	add constraint 约束名  —— 约束的表级形式

例4：

alter table test1
        add constraint pk_test1 primary key(n1)

我们在执行后发现报错。  
**原因：** 设置主键的属性不是非空属性，需要先设置属性非空，再添加主键约束。  
继续执行，执行后仍错，  
**原因：** 是表中已有多个元组在n1上的值均为1，n1的值不唯一。删除表中元组再添加主键。

**注意：** 在已有表上添加主键约束时：  
a）该属性须为非空属性  
b）表中已有数据在该属性上的取值和主键约束不冲突。

## （5）删除约束 ##

格式：	
    	alter table 表名
    	drop constraint 约束名

# 二、唯一约束：unique，实现属性值唯一 #

MYSQL中null值也是不可以重复的。  
唯一约束的用法与主键相同，只需将`primary key`换成`unique`  
例5：

## （1）创建表时设置唯一约束 ##

设置单个属性字段为唯一约束

create table test5
    	(	n1 int unique,
    		n2 int,
    		n3 int );

设置多个属性字段为唯一约束

create table test5
    	(	n1 int,
    		n2 int,
    		n3 int, 
    		constraint uk_test5 unique(n1)
    	)

## （2）删除唯一约束 ##

删除唯一约束，与删除主键方法一样。

格式：	
    	alter table 表名
    	drop constraint 约束名

# 三、用户定义的完整性——check #

CHECK 约束用于限制列中的值的范围。

*  如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
 *  如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。  
    引入：我们来创建一个 stu 表

create table stu
    	(	id int,
    		name nvarchar(10),
    		sex nvarchar(2)
    	);

insert into stu(id,name,sex)
    	values(1,'张三','二');

使得属性的取值满足某个条件，用check。

格式：	check(条件表达式)

条件表达式的写法和查询语句中写法一致。

## （1）创建表时添加check ##

例6：

create table stu
    	(	id int,
    		name nvarchar(10),
    		sex nvarchar(2),
    		constraint ck_stu check(sex='男' or sex='女')
    	);

## （2）给已有表添加check ##

例7：  
限制性别只能取男或女两个值

alter table stu
    	add constraint ck_stu check(sex='男' or sex='女');

**注意：** 添加约束时，表中如果已有数据且和约束条件冲突，需要先删除冲突的元组。

## （3）删除check，和删除主键约束一样 ##

格式：	
    	alter table 表名
    	drop constraint 约束名

# 三、参照完整性——外键（码） #

## （1）给已有表添加外码：该属性不是该表的码，是另一个表的码 ##

格式：	
    alter table 表名
    add constraint fk_表名foreign key(列名) references 表名(列名);

**说明：** 约束名中的表名是参照表的表名，第2个表名是被参照表的表名

例8：

alter table emp
    add constraint fk_foreign key(dept_id) references dept(id);

但是我们会发现出错了。  
**原因：** dept表中的id不是`主键`。给dept添加主键、唯一约束或唯一索引。

alter table dept
    add constraint pk_dept primary key(id)

再添加外码

## （2）建表时创建外码约束 ##

### a) 表级形式 ###

create table e
    (	id int,
    	name varchar(50),
    	dept_id smallint,
    	manager_id int,
    	title varchar(50),
    	salary numeric(11,2),
    	constraint fk_e foreign key(dept_id) references dept(id)
    )

### b)列级约束 ###

create table e
    (	id int,
    	name varchar(50),
    	dept_id smallint constraint fk_e foreign key(dept_id) references dept(id),
    	manager_id int,
    	title varchar(50),
    	salary numeric(11,2),
    )

删除被参照表中的数据时：

insert into e
    select id,name,dept_id,managerid,title,salary
    from emp

delete 
    from dept
    where  id=1

出错：不能删除。  
**原因：** 已经有员工参照1号部门  
修改被参照表中的数据时：

update dept
    set id=20
    where id=1

**出错。**

添加被参照表中的数据时：

insert into dept
    values(13,'aa',3)

可以。对参照表无影响。  
被参照表使用时受限，被参照表上数据的修改、删除受限，添加不受限制。被参照表删除也是受限的。

delete 
    from dept
    where  id=13

注意是受限不是禁止。当不影响参照表时是可以修改和删除数据的。  
删除被参照表：不可以。定义的有外键。  
删除、修改参照表中的数据时：

delete 
    from e
    where id=24
    update e
    set dept_id=20
    where dept_id=1

**出错:** 在dept表中没有20号部门。

insert into e
    values(30,James,20,5,sales,2000)

被参照表使用时受限，参照表上数据的修改、添加受限，删除不受限。  
解决方法：  
**级联删除、级联修改、级联置空：**

*  删除被参照表中的数据时，将参照表中对应的数据也删除，称为级联删除。  
    或者将参照表中对应数据在该属性上的取值置为空值，如果该属性可以取空值的话。
 *  当修改被参照表中的数据时，将参照表中参照对象的该属性值也跟着修改，称为级联修改。  
    或者将参照表中对应数据在该属性上的取值置为空值，如果该属性可以取空值的话。

声明外键时，注明：

后两句之间无关系，可写一个，也可分别设置不同的处理。  
![在这里插入图片描述][202012050001174.gif_pic_center]

[20201204203442318.png]: /images/20221120/4146d7250643452290194e7ac332b6d7.png
[202012050001174.gif_pic_center]: https://img-blog.csdnimg.cn/202012050001174.gif#pic_center