Mysql学习—约束

[toc]

数据表约束概述

数据完整性

  • 对表中所存储的数据是否合法进行的检查。因此可以对已经创建好的表定义完整性约束，或创建新表时就已经进行完整性约束定义。

  • 数据完整性是指数据的精确性和可靠性，防止数据库存在不合语义规定的数据和数据库操作人员错误的输入、输出而造成数据库中存在的错误数据

分类

1. 实体完整性

指关系中的主属性值不能为null且不能有相同的值

2. 参照完整性

两个表的主键和外键的数据应该一致

规则

(1)禁止在从表中插入包含主表中的不存在的关键字的数据行

(2)禁止删除从表中有对应记录的主表记录

(3)禁止会导致从表中相应值孤立的主表中的外键值改变

3. 用户定义完整性

针对某个特定数据库的约束条件

完整性约束类型
完整性约束关键字 含义
NOT NULL 约束字段的值不能为空
DEFAULT 设置字段的默认值
UNIQUE KEY(UK) 约束字段的值唯一
AUTO_INCREMENT 约束字段的值为自动增加
PRIMARY KEY(PK) 约束字段为表的主键，可以作为该表记录的唯一标识
FOREIGN KEY 约束字段为表的外键

一、非空约束(NOT NULL)

语法

1.设置新建表

1
2
3
4
CREATE TABLE  table_name
(
	col_name data_type NOT NULL…………
);

2.设置已存在的表

1
ALTER TABLE table_name MODIFY col_name data_type NOT NULL…………;

二、默认约束(DEFAULT)

语法

1.设置新建表

1
2
3
4
CREATE TABLE  table_name
(
	col_name data_type DEFAULT…………
);

2.设置已存在的表

1
ALTER TABLE table_name MODIFY col_name data_type DEFAULT…………;

三、唯一约束(UNIQUE KEY(UK))

语法

1.设置新建表

1
2
3
4
CREATE TABLE table_name
(
	col_name data_type UNIQUE…………;
);

2.设置已存在的表

1
ALTER TABLE table_name MODIFY col_name data_type UNIQUE…………;

3.删除唯一键

1
ALTER TABLE table_name DROP INDEX index_name;

索引(index_name):一般是唯一约束的字段名

设置自增的注意项：

1.必须是整数类型

2.必须先定义一个键(主键或唯一键)

3.可以使用ALTER TABLE table_name AUTO_INCREMENT=default_value为字段设置自增的初始值1

四、自增约束(AUTO_INCREMENT)

语法

1.设置新建表

1
2
3
4
CREATE TABLE table_name
(
	col_name data_type AUTO_INCREMENT
);

2.设置已存在的表

1
ALTER TABLE table_name MODIFY col_name data_type AUTO_INCREMENT;

五、主键约束(PRIMARY KEY(PK))

关系:

1.子表中的参照值只能从主表中的参照值中选择

2.当主表中的参照值不存在时，子表也不存在

主键-单字段主键

1.创建新表

1
CREATE TABLE table_name(col_name date_type PRIMARY KEY,……)

2.已存在的表

1
ALTER TABLE table_name ADD [CONSTRAING constraint_name] PRIMARY KEY(col_name)

3.删除主键

1
ALTER TABLE table_name DROP PRI

主键-多字段联合主键

1.创建新表

1
2
3
4
5
ACREATE TABLE table_name(col_name data_type,……
                         
[CONSTRAINY constraint_name]
                         
PRIMARY KEY(col_name1,col_name2……)

2.已存在的表

1
ALTER TABLE table_name[CONSTRAINT constraint_name PRIMARY KEY(col_name1,col_name2)]

注意：

多字段联合主键不允许出现两个完全相同的联合主键

六、外键约束(FOREIGN KEY)

外键不能单独设置，只有当设置了主键之后才能设置外键，外键必须参照另一个表的主键

(一) 类型

1.创建新表

1
2
3
4
5
6
7
8
CREATE TABLE table_name
(
col_name data_type,…… //正常c值
    
[CONSTRAINY constrainy_name] FOREIGN KEY(col_name1) //指定外键
    
REFERENCES table_name(col_name2)//外键所在的表
);

FOREIGN KEY：约束的名称，后可接外键名称

REFERENCES：参照，后接另一个表名和该表主键名称

2.已存在的表

1
2
3
4
5
6
7
8
9
10
ALTER TABLE table_name1

ADD CONSTRAINT [constraint_name]
//指定外键名称constraint_name

FOREIGN KEY (col_name1)
//指定了class_id作为外键

REFERENCES table_name (col_name2) 
//指定了这个外键将关联到class表的id列（即class表的主键

3.删除外键

1
ALTER TABLE table_name1 DROP FOREIGN KEY constraint_name

注意：

如果没有外键名称，系统会自动加上
用命令show create table table_name查看

(二) 行为

行为 说明
NO ACTIION</br>(RESTRICT) 当在父表中删除/更新对应记录时，首先检查该记录是否存在对应外键，如果由则不允许删除/更新。
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否存在对应外键，如果有则删除/更新外键在子表中的记录。
SET NULL 当在父表中删除对应记录时，首先检查该记录是否存在对应外键，如果有则设子表中外键为null(要求外键允许null)
SET DEFAULT 父表有变更时，子表将外键列设为一个默认的值(Innodb不支持)

七、检查约束

在8.0.16版本之后支持，保证字段满足某一个条件

1.创建新表

1
2
3
4
CREATE TABLE table_name(
	color_name  data_type……
    check( 条件1 && 条件2)
)

2.已存在的表

1
2
3
ALTER TABLE Students

ADD CONSTRAINT [constraint_name] chk_age CHECK (年龄 >= 18);