欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mysql数据库完整性约束讲解

程序员文章站 2022-06-22 13:40:35
一、数据完整性的概念 1、目的 为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不...

一、数据完整性的概念

1、目的

为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。

2、概念

约束是用来确保数据的准确性和一致性。数据的完整性就是对数据的准确性和一致性的一种保证。
数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。

3、分类

1、数据实体完整性(唯一约束、主键约束、标识列 )
2、字段完整性(限制数据类型、检查约束、外键约束、默认值、非空约束 )
3、引用完整性(外键约束 )
4、自定义完整性(规则、存储过程、触发器)

二、自动增长

1、自动增长

在数据库中,我们常用到唯一编号以表示记录,在mysql中可以通过数据列的auto_increment属性来自动增长.
示例:

Create table 表名(Id int not null auto_increment,Name varchar(20),Primary key(id))engine=innodb default charset=utf8;

2、修改自动增长列自动增长值的开始值

Alter table 表名 auto_increment=值(从什么数值开始增长);

alter table student auto_increment=10

三、主键约束

1、主键(primary key):

主键是表中实现记录唯一性的字段(唯一性,非空),一个表中只能有一个主键,如果建表的时候没有明确定义not null,mysql会隐含的定义这些列为not null。

2、语法:Create table 表名(列名 数据类型,…..,primary key(主键名(列名)));

Alter table 表名 add primary key(主键名(列名));
Alter table 表名 add CONSTRAINT 主键名称 PRIMARY key(主键);

alter table students add primary key (s_id)
-- 会报错 s_id主键不允许为null
insert into students(s_id,s_name,age,height) values(null,'gmcc',18,170)
-- 不会报错, s_id主键可以插入一个空字符串
insert into students(s_id,s_name,age,height) values('','gmcc',18,170)

四、外键约束

1、外键:

可以使得两张表关联,保证数据的一致性和实现一些级联操作外键是子表里的一个字段,引用父表里的主键,一个被定义为外键的字段用于引用另一张表里的主键。
注意:外键是主表中的一个字段,而外键名是不会在主表和从表中出现的。

2、语法:alter table 从表名 add CONSTRAINT 外键名称 FOREIGN KEY(从表的某个列名–外键) REFERENCES 主表名(主表中的和外键对应的字段);

外键名取名规范:FK_主表名
ALTER TABLE students
ADD CONSTRAINT fk_class FOREIGN KEY (class_id)
REFERENCES class (class_id) on delete CASCADE ;

3、要求:

A:两个表必须是innodb表。
B:外键列必须建立索引。
C:外键关系的两个表的列必须是数据类型相似,可以相互转换的,比如(int 跟 tinyint)可以,而int跟char则是不可以的。

4、关键字:

foreign key(列名) 引用从表某列。
references 父表表名(父表的列名)
constraint 指定约束的名称。在数据库中应是唯一的。如果没有指定,那么系统为默认给你生成一个约束名。

5、级联删除

在添加外键时 添加 on delete cascade ,在删除主表中的外键是会进行级联删除。
on delete /on update cascade 级联更新删除数据。
在实际工作中很少建立主外键关系,主外键关系的约束会影响到执行性能,并且级联删除可能会造成大量数据的误删。

6、注意:

1、外键的作用:能够保证数据的一致性。
2、从表中的外键值必须在主表中存在,建立主外键关系时,必须存在主表。先建立主表,再是从表。
3、如果插入的从表数据外键字段的值在主表中不存在则会报如下错误
Cannot add or update a child row: a foreign key constraint fails (school.students, CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (class_id))
4、删除带有主外键关系的表时,先删从表再删主表数据。

五、唯一约束 UNIQUE

1、唯一性约束要求表里某个字段的值在每条记录里都是唯一的。
2、语法:alter table 表名 add constraint 唯一约束名 unique (列名);
例:

alter table students add constraint un_name unique(s_name);

3、建表的时候添加唯一约束的方式:直接在需要唯一约束的字段后加上 unqiue。
例:

create table test(t_id int not null primary key,t_name varchar(10) not null unqiue);

六、非空约束 NOT NULL

字段定义了NOT NULL那么该字段就不允许NULL值出现,也可以说是每个字段都必须有值,没有指定的话就默认是NULL。

七、检查约束 CHECK

1、语法:alter table 表名 add constraint 约束名 check(表达式);
例:

alter table students add CONSTRAINT CHK_age CHECK (s_age>18);
alter table students add CONSTRAINT CHK_SEX CHECK (s_sex in ('男','女'));

八、删除约束

删除主键时必须先删除自增长。
– 删除自增长
Alter table 表名 CHANGE 自增列 自增列 数据类型;
例如:
ALTER table color CHANGE color_id color_id int(20);
– 删除主键
Alter table 表名 drop PRIMARY KEY;
例如:

ALTER table color DROP PRIMARY KEY;

– 删除约束
ALTER table 表名DROP CONSTRAINT 约束名;
–删除外键:
ALTER table 表名 drop FOREIGN KEY 外键名;

alter table students drop foreign key fk_class;