MySQL常见约束-学习笔记_mysql 索引类型 check是什么-优快云博客

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联

列的值
```
    在从表添加外键约束，用于引用主表中某列的值
```
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：
```
1.创建表时
2.修改表时
```

约束的添加分类：

列级约束：
    六大约束语法上都支持，但外键约束没有效果

表级约束：

  除了非空、默认，其他的都支持

主键和唯一的大对比：

    保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
主键    √        ×            至多有1个           √，但不推荐
唯一    √        √            可以有多个          √，但不推荐

外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表

查看约束 SHOW CREATE TABLE table_name;

貌似desc / describe table;也可以展示

主键约束（primary key）

主键约束最显著的特征是主键列中的值是不允许重复的，通过主键约束可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只有一个 PRIMARY KEY 约束，且 PRIMARY KEY 约束中的列不能接受NULL值。

ALTER TABLE tab_name ADD CONSTRAINT pk_name PRIMARY KEY(字段名);
# 设置该字段为主键，主键约束名称为pk_name

创建主键约束的几种方式

-- 1.创建表的时候指定主键约束
CREATE TABLE `table_name` (`id` int PRIMARY KEY,-- 设置主键`name` varchar(20));

CREATE TABLE `table_name` (`id` int,`name` varchar(20) ,PRIMARY KEY (`id`) -- 设置主键
);

CREATE TABLE `table_name` (`id` int,`name` varchar(20) ,constraint pk primary key(id) -- 设置主键
);

-- 2.修改某一列为主键
ALTER TABLE 表名称 ADD [CONSTRAINT] PRIMARY KEY(id);

ALTER TABLE 表名称 MODIFY [COLUMN] 字段名 属性 PRIMARY KEY; -- 修改列类型

ALTER TABLE 表名称 CHANGE [COLUMN] 字段名 字段名 属性 PRIMARY KEY; -- 修改列名称和类型

删除主键约束

ALTER TABLE 表名称 DROP PRIMARY KEY; 
# [mysql 8.0.22 之后的版本]

ALTER TABLE 表名称 DROP INDEX PRIMARY KEY; 
# [mysql 8.0.22 之前的版本]

自增长列（标识列）(auto_increment)

自增长列是int类型的，其值是由数据库自动维护的，是永远都不会重复的，因此自增长是最适合作为主键列的。在创建表时，通过 AUTO_INCREMENT 关键字来标识自增长列，在MySQL数据库中自增长列必须是主键列。

特点：

标识列必须和一个Key搭配（Key指主键、唯一、外键…）
一个表最多有一个标识列
标识列的类型只能是数值型
标识列可以通过 SET auto_increment_increment = 3; 设置步长（全局，退出数据库重新进入会恢复默认值），可以通过插入行时手动插入标识列值设置起始值。

CREATE TABLE goods(
    no INT PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(10));
-- 直接设置自增长

ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 AUTO_INCREMENT; 
ALTER TABLE 表名 CHANGE 列名 列名 列类型 AUTO_INCREMENT;
-- 修改为自增长列

ALTER TABLE 表名 MODIFY [COLUMN] id int;
-- 删除自增长列

联合主键(composite primary key)

联合主键（Composite Primary Key）是指在数据库表中，由多个列共同组成的主键，用来唯一标识表中的每一行数据。它的作用类似于单一列的主键，但不是由单个列组成，而是由多个列组合而成。联合主键可以确保表中的每一行都具有唯一性，并且每个列组合的值都不会重复

在创建表时，可以在列定义中指定多个列作为联合主键。

CREATE TABLE 表名 (
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
PRIMARY KEY (列名1, 列名2, 列名3)
);


ALTER TABLE 表名 ADD [CONSTRAINT] PRIMARY KEY (列名1, 列名2, 列名3);
-- 修改列的时候创建

ALTER TABLE 表名称 DROP PRIMARY KEY;
-- 删除

联合主键在以下情况非常有用：

当单个列无法唯一标识表中的每一行，但多个列组合在一起可以唯一标识每一行数据时。
提高查询性能：联合主键可以更有效地支持涉及多个列的查询，避免创建额外的索引。
在具有多个外键的关联表中，可以使用联合主键来确保外键引用的准确性。

联合主键要求每个列组合的值都是唯一的

唯一约束（unique）

对于非主键列中的值也要求唯一性时，就需要唯一约束

-- 创建表时
CREATE TABLE `table_name` (
    `id` int,
    `name` varchar(20) UNIQUE);
# 唯一约束

CREATE TABLE `table_name` (
`id` int,
`name` varchar(20),
 CONSTRAINT uq UNIQUE(name) );
                #唯一约束
# syntax: constraint name unique(col_name);


-- 修改表
ALTER TABLE 表名 ADD UNIQUE(列名称);
ALTER TABLE 表名 ADD CONSTRAINT [constraint_name] UNIQUE(列名称);
# 只能创建唯一约束


ALTER TABLE 表名 CHANGE [COLUMN] 列名 列名 类型 UNIQUE;
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 UNIQUE;
# alter table modify / change 

-- 删除唯一约束
ALTER TABLE 表名称 DROP INDEX 设置唯一时的名称;
# 如果没有设置约束名称，名称默认是字段名

唯一约束允许有多个 NULL 值

默认约束（default value）

为列中的值设置默认值， DEFAULT value

-- 创建表时
CREATE TABLE `table_name` (
`id` int DEFAULT value,
`name` varchar(20) unique);

-- 修改表
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 DEFAULT 默认值;
ALTER TABLE 表名 CHANGE 列名 列名 列类型 DEFAULT 默认值;

-- 删除
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型;
ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT; 
# 【8.0.23以上的版本】
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT NULL;
# 【8.0.23以前的版本】

如果已经设置了值，默认值就无效了

非空约束(not null)

NOT NULL ：非空，用于保证该字段的值不能为空。

-- 创建表时
CREATE TABLE `table_name` (
`id` int NOT NULL, 
`name` varchar(20));
# 非空约束


ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 NOT NULL;
ALTER TABLE 表名 CHANGE 列名 列名 列类型 NOT NULL;
# 修改现有表格

ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 [null];
# 删除

修改列时必须保证列中的所有元素都是非null的，否则会报错

检查约束（check）

检查约束允许你在声明的条件中将数据插入到对应的列中

CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100),
CHECK (age >= 18));
-- 添加检查约束：年龄必须大于等于18


ALTER TABLE 表名 CHANGE 列名 列名 列类型 CHECK(condition);
ALTER TABLE 表名 MODIFY 列名 列类型 CHECK(condition);
ALTER TABLE 表名 ADD CONSTRAINT 列名 CHECK(condition);
-- 修改表的时候

ALTER TABLE 表名 DROP CONSTRAINT 约束名;
-- 删除检查约束

外键约束（foreign key）

MySQL FOREIGN KEY 外键约束 (w3schools.cn)

被应用数据的表被叫做主表

应用数据的表叫从表

用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

-- 创建表时
CREATE TABLE `table_name` (
`id` int NOT NULL,
`name` varchar(20),
`rid` int,
CONSTRAINT fk_a_b FOREIGN KEY(rid) REFERENCES b(id));
# constraint 为约束条件，必须写 ，后面的是约束名字
# foreign key（xx） xx为从表的某一列
# reference XX（ee） xx 为主表，ee为主表的某一列


ALTER TABLE 从表表名 ADD [CONSTRAINT] 约束名称 FOREIGN KEY (从表字段)
REFERENCES 主表表名(主表字段);

ALTER TABLE goods ADD [CONSTRAINT] fk_category_no FOREIGN KEY (category_no) REFERENCES category(no);


-- 删除
ALTER TABLE 表名称 DROP FOREIGN KEY 索引名 ;
alter table table_name foreign key key_name;
# 设置外键时的名称

外键列类型需要与引用列类型一致
外键列的值必须是主表中引用列的值或者 NULL
一个表可以有多个外键列
从表列可以随便删除
删除主表数据时，会先检查从表中有没有对此数据的关联，如果有就不能直接删除
- 在设置外键的时候后面添加 on delete cascade / on update cascade 在删除/更新主表时，级联删除/更新外键列的数据
- 在设置外键的时候后面添加 on delete set null / on update set null 在删除/更新主表时，外键列的值会变成null