mysql约束

# 约束

用于限制数据库结构中存储的数据。目的是为了保证数据库中数据的正确、有效性和完整性。

常见约束：

|约束|作用|关键字|
|---|---|---|
|非空约束|限制字段数据不能为NULL|NOT NULL|
|唯一约束|限制字段的数据唯一，不能重复|UNIQUE|
|主键约束|设置字段为主键，主键满足非空和唯一约束|PRIMARY KEY|
|默认约束|字段默认值|DEFAULT|
|检查约束|保证字段值满足条件|(8.0.16版本后）,CHECK|
|外键约束|与其他表建立连接的关系|FOREIGN KEY|

## 非空约束
```sql
# 关键字 NOT NULL
# 可以在创建时在字段后加上NOT NULL，也可以通过ALTER TABLE 修改时增加非空约束
# 创建时添加
CREATE TABLE IF NOT EXISTS emp(
id int NOT NULL AUTO_INCREMENT comment "编号",
uid varchar(20) comment "工号",
name varchar(20) comment "姓名",
age int comment "年龄")
# 修改
ALTER TABLE emp MODIFY name varchar(20) NOT NULL;
# 取消非空约束,再修改为NULL即可
ALTER TABLE emp MODIFY name varchar(20) NULL;
```

## 唯一约束
```sql
# 设置字段值唯一，不能出现重复
# 创建字段时添加
CREATE TABLE IF NOT EXISTS emp(
id int NOT NULL AUTO_INCREMENT comment "编号",
uid varchar(20) comment "工号",
name varchar(20) UNIQUE comment "姓名",
age int comment "年龄");

# 通过ALTER TABLE添加约束
ALTER TABLE emp MODIFY name varchar(20) UNIQUE;

# 删除唯一约束
ALTER TABLE emp DROP INDEX name;
```

## 主键约束

```sql
# 主键约束，主键是一行记录的唯一标识，满足非空和唯一性约束
# 创建表时指定主键字段,指定编号字段为主键
CREATE TABLE IF NOT EXISTS asset(
id int PRIMARY KEY AUTO_INCREMENT comment "编号"
);

# 修改表的主键字段
ALTER TABLE employee ADD PRIMARY KEY(id);

# 删除主键设置
# 1.使用ALTER TABLE语句
ALTER TABLE 表名 DROP PRIMARY KEY;
# 2.使用DROP INDEX语句
DROP INDEX 主键名 ON 表名;

# 通过PRIMARY KEY 在字段名后指定一个字段为主键或者通过单独一行PRIMARY KEY(id,name)设置多个字段为联合主键
CREATE TABLE `tableName` (
`col1` INT NOT NULL,
`col2` INT NOT NULL,
`col3` VARCHAR(20),
PRIMARY KEY(`col1`,`col2`)
);
```

## 默认约束
```sql
# 通过默认约束DEFAULT设置字段默认值
# 添加
CREATE TABLE IF NOT EXISTS emp(
id int NOT NULL AUTO_INCREMENT comment "编号",
uid varchar(20) comment "工号",
name varchar(20) comment "姓名",
age int comment "年龄",
gender varcher(1) DEFAULT '男'
);
# 修改表时添加约束
ALTER TABLE emp MODIFY gender varchar(1) DEFAULT('男');
# 删除，直接取消DEFAULT即可
ALTER TABLE emp MODIFY gender varchar(1);
```

## 检查约束
```sql
# CHECK可以检查数据记录满足一定条件才能录入，不过好像测试后不生效，网上说这不是真正支持的特性，需要通过触发器等其他方式实现
ALTER TABLE emp MODIFY age int CHECK (age > 0 and age < 120) comment '年龄';
```

## 外键约束
```sql
# 外键FOREIGN KEY设置与其他表查询时的数据一致性问题
# 一个表(父表)的主键是另外一个表的外键(子表)
# 添加外键
CREATE TABLE 表名(
    字段名 数据类型
    [CONSTRAINT] [外键名称] FROEIGN KEY (外键字段名) REFERENCES 主表(主表列名)
)
# 示例
CREATE TABLE IF NOT EXISTS use_assets(
id int PRIMARY KEY AUTO_INCREMENT comment 'id',
user_id int comment '用户ID',
asset_id int comment '资产ID',
address varchar(20) comment '存放部门',
FOREIGN KEY(user_id) REFERENCES emp(id),
FOREIGN KEY(asset_id) REFERENCES asset(id)
);
# 修改表时添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名);

# 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

# 定义更新外键数据的操作
# NO ACTION,更新父表数据，如果有外键数据则不允许删除或更新
# RESTRICT，更新父表数据，如果有外键数据则不允许删除或更新
# CASCADE，更新父表数据，如果有外键数据则同步删除或更新子表数据
# SET NULL，更新父表数据，如果有外键数据则设置为NUL(需要子表允许为NULL)
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

# 使用外键可以在物理层验证数据完整性和一致性，但是每次更新数据都需要查询关联表是否有数据，可能会降低性能
```