mysql 数据完整性约束 mysql数据完整性约束题

文章目录

• 一：实体完整性

• （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)

我们会发现，数据表中出现了三条一样的数据

但是，在大多数情况下，我们要在表的每一行有一个唯一标识，使得每一行中有一个或多个属性字段值不相同，所以我们引入主键（码） 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)

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

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

声明外键时，注明：

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)
	on delete cascade | set null
	on update cascade | set null
)

后两句之间无关系，可写一个，也可分别设置不同的处理。