mysql数据库中有哪些数据完整性?

一.数据完整性的概念 1.什么是数据完整性 数据完整性是指存储在数据库中的数据能正确反映实际情况,规定输入的数据不能是无效值、错误值或者乱码等。 2.数据完整性的类型 ① 实体完整性:标识符或者主键的完整性,使其唯一。 ② 域的完整性:限制类型、格式和取值范围等。 ③ 引用完整性:保持原表和引用表数据的一致性。 ④

一.数据完整性的概念

1.什么是数据完整性

数据完整性是指存储在数据库中的数据能正确反映实际情况,规定输入的数据不能是无效值、错误值或者乱码等。

2.数据完整性的类型

① 实体完整性:标识符或者主键的完整性,使其唯一。

② 域的完整性:限制类型、格式和取值范围等。

③ 引用完整性:保持原表和引用表数据的一致性。

④ 自定义完整性:用户自定义的业务规则。



3.数据完整性的实现方式

实体完整性:主键约束、唯一值约束

域完整性:检查约束、默认值约束

引用完整性:外键约束

用户自定义完整性:以上约束的自由组合

4.什么是约束及Mysql常用约束

数据完整性约束也就是创建表(creat table)时的约束条件,是用来限制属性或者表中数据的,也可以通过修改语句(alter table)修改约束条件,或者删除表中属性的约束。

5.约束的分类

约束可分为行级约束和表即约束,两者本质上是一样的,只是约束的效果和范围不同。行级约束只对表中的一个属性列起作用,而表级约束可以对表中的多个属性列起作用。

非空、默认、自增列约束一般为行级约束:

主键(联合主键)、外键、唯一约束时标记约束:

 二.完整性约束

1.非空约束:关键字:not null

创建非空约束的语法格式:

create table <table_name>(

name1 int not null,

name2 varchar(20) not null

);

#创建一个表,使编号和名称不能为空

eg:

create table staff( -- 员工表

staff_id int not null, -- 工号(不能为空)

staff_name char(30), -- 员工姓名

staff_gender char(4), -- 员工性别

staff_age int -- 员工年龄

);

insert into staff values(1,"张三","男",21); -- 正常运行

insert into staff (staff_name) values("李四"); -- Error 必需赋值工号

--对于原表可以修改(添加或去除)其中属性的约束
alter table staff modify column staff_name char(30) not null;--添加
alter table staff modify column staff_name char(30);         --去除
alter table staff modify column staff_id int;         --去除

--如果属性的取值已经存在空值,则不允许为这个属性添加非空约束:如果属性已经存在非空约束,则不允许为空值

2.唯一约束:指定某列或者某几列数据的取值不能重复。

                   (这里应为两部分,还有一个复合唯一约束,而实际上也是以复合唯一约束为主)

 现在我们先来简单看看非符合唯一约束的简单概述。

关键字:unique

①创建表时添加唯一约束

create table teacher_tb(

t_no int unique,

t_name varchar(20)

);

②指定列添加唯一约束:

alter table <表名> add 约束类型(属性名);

#eg:可以通过constraint给约束名取别名

alter table <表名> add constraint <约束名> unique (属性名);

③删除唯一约束:

alter table <表名> drop index (属性名/约束名);

eg:

--创建一个数据表,使表中某个属性的取值不能重复
create table staff( -- 员工表

staff_id int unique, -- 工号(唯一)

staff_name char(30), -- 员工姓名

staff_gender char(4), -- 员工性别

staff_age int -- 员工年龄
);

--以修改表结构的方式,为某个属性添加唯一约束
alter table staff add unique(staff_id);

insert into staff values(1,"李四","男",22);
--运行此段会Error,因为在上文中插入工号为1的张三

insert into staff values(2,"李四","男",22);
--此段与上文不冲突,可成功运行

--以修改表结构的方式,去掉某个属性唯一约束
alter table staff drop index staff_id;

复合唯一约束(在唯一约束之上做些许更改)

创建复合唯一约束(表级约束)

可以对多个列进行唯一约束,约束两个及以上的字段(属性)不能同时一致。

①在创建表时添加复合唯一约束的方法

constraint <约束名> unique ( [属性1],[属性2]·····[属性n] );

②在创建表后添加复合唯一约束的方法

alter table <表名> add constraint <约束名> unique ( [属性1],[属性2]·····[属性n] ):

#这里也就是约束属性1、属性2到属性n中可以允许其中存在取相同值的属性,但也不能同时相同,至少要有一个不同。

#数据库默认第一个非空且唯一的属性为主键


--创建一个数据表,使表中某几个属性的取值不能同时重复
create table staff( -- 员工表

staff_id int, -- 工号(唯一)

staff_name char(30), -- 员工姓名

staff_gender char(4), -- 员工性别

staff_age int, -- 员工年龄
constraint uni_idname unique(staff_id,staff_name) --添加复合唯一约束
);
--关键字constraint用于给约束取别名(也可以不取,一般都要取)

--以修改表结构的方式,为某个属性添加复合唯一约束
alter table staff add constraint uni_idname unique(staff_id,staff_name);

--以修改表结构的方式,去掉某个属性唯一约束
alter table staff drop index uni_idname; --通过别名来删除约束

--如果属性的取值已经存在重复值,那么就不允许为这个属性添加唯一约束

三.主键约束

1.主键约束

主键约束就是非空+唯一约束,且每个表只能有一个主键约束(表级),在多列联合的主键约束时,联合主键的值不能同时重复。

关键字:primary key(表级)

①创建表时设置单列主键

create table <表名>(

属性名 数据类型 primary key, -- 设置主键约束

属性名 数据类型 not null

)

②设置多个属性为联合主键

alter table <表名> add [constraint <约束名>] primary key ( [属性1],[属性2]·····[属性n] ):

③删除主键约束

alter table < 表名> drop primary key; #直接删除主键

--创建一个数据表,使表中某属性成为主键
create table staff( -- 员工表

staff_id int primary key, -- 工号(唯一)

staff_name char(30), -- 员工姓名

staff_gender char(4), -- 员工性别

staff_age int -- 员工年龄
-- 或者primary key(staff_id) 该方式一般是添加联合主键时用到
);

--以修改表结构的方式,为某个属性添加主键约束
alter table staff add primary key(staff_id);
--如果属性(staff_id)的取值是空值或者重复,那么此段程序会报错

--以修改表结构的方式,去掉某个属性唯一约束
delete from staff where staff_id = 1 and staff_name = "李四"

--以修改表结构的方式,为某个属性添加联合主键约束
alter table staff add primary key(staff_id,staff_name);

2.自增列

自增列就是在插入数据时,如果不规定属性的值,其值就会根据上一个属性的值自动加1,如果上一个属性值为空或没有上一个属性,自增列会默认初始值为1

关键字:auto_increment(行级约束)

①创建表时添加自增列

create table <表名>(

id int primary key auto_increment,
);

②添加自增列

alter table <表名> modify column <属性名> <数据类型> auto_increment;

③删除自增列

alter table <表名> modify column <属性名> <数据类型>;

create table staff( -- 员工表

staff_id int primary key auto_increment, -- 工号(唯一)

staff_name char(30), -- 员工姓名

staff_gender char(4), -- 员工性别

staff_age int -- 员工年龄

)auto_increment = 100; -- 可以自主规定自增列的初始值为100(从100开始)

--以修改表结构的方式,为某个属性添加自增列约束
alter table staff modify column staff_id int auto_increment;

--以修改表结构的方式,去掉某个属性自增列约束
alter table staff modify column staff_id int not null;--此语句会删除这个属性的全部约束,如果需要保留某个约束则需在后面写上它的关键字,即如果不写"not null"那么在删除时会一起被删掉

------------------------------------------------------------分割线------------------------------------------------------------

接下来就不举例子了,与上面是大同小异的

3.默认值约束

给带有默认值约束的属性添加数据时,如果没有给定属性值,会自动添加一个默认值。

关键字:default

①在创建表时设置默认值约束

create table <表名>(
name varchar(20) not null
sex varchar(2) default '男'; --姓名默认为'男'
);

②添加/删除默认值约束

alter table <表名> modify column <属性名> <属性类型> [default <默认值>];

4.检查约束

顾名思义就是检查列中属性的取值范围,判断字段的值是否为指定的值,如果不是就不允许数据的插入或修改。

关键字:check

age int check (age>=0 and age<=150),--age约束在0到150之间

sex varchar(2) check(sex='男' or sex=’女‘)--sex约束为男或者女

--以修改表结构的方式为某个属性添加检查约束
alter table staff add constraint check_gender check(staff_gender="男" or staff="女");

--删除
alter table <表名> drop constraint <检查约束名>;

四.外键约束

外键约束是指一个表中的属性值参考另一个表中的主属性的值,也就是外键值参考主键值,由于外键约束时表级,所以在创建参考表之前要先创建被参考表

①创建表时建立外键约束

constraint <约束名> foreign key(<属性名>) references 被参考表(<被参考表属性名>)

②添加外键约束

alter table <表名> add constraint <约束名> foreign key (<属性名>) references 被参考表(<被参考表属性名>);

③删除

alter table <表名> drop constraint <约束名>; --根据约束名删除约束

--外键约束用于描述实体集之间的联系,表示参考完整性,外键值参考其他表的主键
--在创建外键所在的参考表之前,需要先创建被参考表
eg:
--部门表(父表)
create table department_tb(
d_id int primary key auto_increment, --部门编号
d_name char(20) not null, --部门名称
d_number int default 0 --部门员工人数
)auto_increment = 100;

--插入任意部门表数据
insert into department_tb(d_name) values("行政部"),("人事部"),("财务部"),("生产部"),
("技术部"),("销售部");

--员工表
create table staff_tb(
staff_id int primary key auto_increment, --工号
staff_name char(30) not null, -- 姓名
staff_did int, --部门编号(外键:表示员工属于哪个部门)
staff_gender char(4) check(staff_gender = '男' or staff_gender = '女'), --性别
staff_age int check(staff_age >=18 and staff_age <= 60), --年龄
constraint for_staff_did foreign key(staff_did) references department_tb(d_id) --外键约束
)auto_increment = 1000;

知秋君
上一篇 2024-08-24 16:36
下一篇 2024-08-24 16:02

相关推荐