数据完整性和约束

为什么要使用约束:

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：
实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围”男/女”
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

约束: 对表中数据的强制性限制

分类

按约束字段数分: 单列约束 多列约束

按约束范围分: 列约束<指定字段后> 表约束<表中所有字段声明后>

按约束作用分:

约束名 释义
not null 非空约束
unique 唯一约束
primary key 主键约束
foreign key 外键约束
check 检查约束
default 默认值

约束可以在创建表 或 修改表时使用

查看约束

  1. SELECT *
  2. FROM information_schema.TABLE_CONSTRAINTS
  3. WHERE table_name = '表名';

NOT NULL 非空约束

  1. 限制字段<单个列>值不能为空 NULL , 不指定时则所有类型列都可以为空 NULL
  2. ‘’ != NULL , 0 != NULL
  3. 一张表可以有多个约束
  4. 创建一个表 id 字段不能为空

    使用

    ```sql

    创建时直接使用 约束 id 不能为空

    CREATE TABLE n_null( id INT NOT NULL， note VARCHAR(20) ) CHARACTER SET ‘utf8mb4’;

DESC n_null;

修改时使用 约定 name不能为空

为表中增加字段 且此字段不能为空

ALTER TABLE n_null ADD name VARCHAR(20) NOT NULL ; ALTER TABLE n_null MODIFY note VARCHAR(20) NOT NULL ;

  1. <a name="Hf9nI"></a>
  2. ### 删除约束
  3. ```sql
  4. # 修改表中 id 字段 使其可以为空
  5. ALTER TABLE n_null MODIFY id INT;

修改表中 id 字段 使其可以为空 , 特殊: 当当前数据中已经有了 NULL 时再将存在NULL的字段 设置为非空 时将设置失败 Data truncation: Invalid use of NULL value

约束生效

  1. ## 当增加了 NOT NULL 限制时必须指定字段值否则报错  Column 'name' cannot be null
  2. INSERT INTO n_null(id, name)
  3. VALUES (1, NULL);
  4. UPDATE n_null SET name = NULL
  5. WHERE id = 1;
  7. ## 当增加了 NOT NULL 时未指定字段值默认使用默认值而默认值,未设定时默认是NULL Field 'name' doesn't have a default value
  8. INSERT INTO n_null(id)
  9. VALUES (1);

UNIQUE 唯一约束

  1. 限制字段同一种值只能出现一次
  2. 一张表可以有多个约束
  3. 可以在声明唯一约束的字段中添加 NULL 且 NULL值可以存在多个不受唯一约束影响

    使用

    ```sql

    创建时使用

    CREATE TABLE u_nique( id INT COMMENT ‘id’ UNIQUE , name VARCHAR(20) COMMENT ‘姓名’ );

DESC u_nique;

创建表级约束

CREATE TABLE u_test( id INT , name VARCHAR(20), remark VARCHAR(60), age INT,

  1. # 约束  指定约束名 类型+字段
  2. CONSTRAINT uk_id_name UNIQUE (id,name), # 复合唯一约束
  3. CONSTRAINT uk_rk UNIQUE (remark)

);

修改 或添加约束 【1】 列级

ALTER TABLE u_nique MODIFY id INT UNIQUE ;

修改 或添加约束 【2】 表级 如果不指定约束名默认使用字段名作为约束名

ALTER TABLE u_nique ADD CONSTRAINT uk UNIQUE (id);

  1. <a name="ielaw"></a>
  2. ### 删除约束
  3. ```sql
  4. ## 删除唯一约束
  5. /*
  6.     添加唯一性约束的列上也会自动创建唯一索引
  7.     删除唯一约束只能通过删除唯一索引的方式删除
  8.     删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样
  9.     如果创建唯一约束时未指定名称 单列--默认和列名相同 , 
  10.     组合列--默认和 `CONSTRAINT uk UNIQUE (第一列,第二列)` 中的第一列名相同,
  11.     若自定义有约束名则使用自定义名
  12. */
  13. ALTER TABLE u_nique DROP INDEX id_2;

约束生效

  1. # 正常添加数据
  2. INSERT INTO u_nique(id , name)
  3. VALUES (1, 'aa'), (2, 'bb');
  4. SELECT *
  5. FROM u_nique;
  7. # 存在id=1的数据时 添加数据 id=1 则报错 Duplicate entry '1' for key 'u_nique.id'
  8. INSERT INTO u_nique(id, name)
  9. VALUES (1, 'cc');
  10. INSERT INTO u_nique(id, name)
  11. VALUES (null, 'cc');
  13. # 修改同理  Duplicate entry '1' for key 'u_nique.id'
  14. UPDATE u_nique SET id = 1
  15. WHERE id = 2;
  17. # 正常添加数据
  18. INSERT INTO u_test(id, name, remark, age)
  19. VALUES (1,'a','a',10);
  21. # 因为是复合唯一约束 所有  id + name 联合起来只要不重复即可
  22. INSERT INTO u_test(id, name, remark, age)
  23. VALUES (3,'a','c',10);
  25. # Duplicate entry '1-a' for key 'u_test.uk_id_name'
  26. INSERT INTO u_test(id, name, remark, age)
  27. VALUES (1,'a','c',10);
  29. # Duplicate entry 'a' for key 'u_test.uk_rk'
  30. INSERT INTO u_test(id, name, remark, age)
  31. VALUES (2, '11', 'a', 11);

PRIMARY KEY 主键约束

  1. 唯一标识表中的一行记录 相当于 唯一约束+非空约束 非空且唯一
  2. 个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
  3. 主键约束对应着表中的一列或者多列（复合主键）
  4. 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
  5. MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
  6. 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
  7. 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

    使用

    ```sql

    创建时使用

    CREATE TABLE t_pk(

    id INT PRIMARY KEY 单只主键约束

    id INT, name VARCHAR(10), job_num INT, CONSTRAINT PRIMARY KEY pk_name (id, job_num) COMMENT ‘复合主键约束’ # 指定了约束名 但是并不会生效 名字还是PRIMARY );

当表中没有主键时 可修改字段添加主键约束

ALTER TABLE c_tb ADD CONSTRAINT PRIMARY KEY (id);

  1. <a name="sppMT"></a>
  2. ### 删除约束
  3. ```sql
  4. ## 删除主键 (不建议,实际开发不可能删除) 
  5. ALTER TABLE c_tb DROP PRIMARY KEY ;

删除主键约束 后此字段依然是 NOT NULL

约束生效

  1. /*# 主键任一个列都不能为空 Field 'id' doesn't have a default value , Column 'job_num' cannot be null , Column 'id' cannot be null*/
  2. INSERT INTO t_pk(name, job_num)
  3. VALUES ('xx', 12);
  4. INSERT INTO t_pk(id, name, job_num)
  5. VALUES (1, 'aa', NULL);
  6. INSERT INTO t_pk(id, name, job_num)
  7. VALUES (NULL, 'cc', 111);
  9. # 主键不能重复 Duplicate entry '1-1' for key 't_pk.PRIMARY'
  10. INSERT INTO t_pk(id, name, job_num)
  11. VALUES(1,'aa', 1);
  12. INSERT INTO t_pk(id, name, job_num)
  13. VALUES(1,'aa', 1);
  15. # 复合主键 需要 复合列组合起来不重复即可
  16. INSERT INTO t_pk(id, name, job_num)
  17. VALUES(1,'aa', 2);

AUTO_INCREMENT 自增长

  1. 一个表最多只能有一个自增长列
  2. 当需要产生唯一标识符或顺序值时，可设置自增长
  3. 自增长列约束的列必须是键列（主键列，唯一键列）
  4. 自增约束的列的数据类型必须是整数类型
  5. 如果自增列指定了 0 和 null , 会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

    使用

    ```sql

    修改时使用

    ALTER TABLE c_tb MODIFY id INT PRIMARY KEY AUTO_INCREMENT;

创建时使用

CREATE TABLE t_at (id INT PRIMARY KEY AUTO_INCREMENT);

创建时使用

CREATE TABLE t_at1(id INT AUTO_INCREMENT, CONSTRAINT PRIMARY KEY (id));

  1. **添加时 此字段若不指定值(不填 0 NULL) 将自动按最大值增长**
  3. **必须配合 （主键列，唯一键列）否则报错 : **<br />** Incorrect table definition; there can be only one auto column and it must be defined as a key**
  4. ```sql
  5. CREATE TABLE t_tt (id INT AUTO_INCREMENT);

删除自增

  1. # 修改时使用 可以重置约束 即 删除
  2. ALTER TABLE c_tb MODIFY id INT PRIMARY KEY ;

约束生效

  1. SELECT *
  2. FROM t_at; # 没有数据
  4. # 添加id 使其子增长
  5. INSERT INTO t_at VALUES (0),(0),(0),(0); 
  6. # 查询
  7. SELECT *
  8. FROM t_at; # 1 2 3 4
  10. # 删除最大值
  11. DELETE
  12. FROM t_at
  13. WHERE id = 4;
  14. # 查询
  15. SELECT *
  16. FROM t_at; # 1 2 3
  18. # 重新添加
  19. INSERT t_at VALUES (0);
  20. # 查询
  21. SELECT *
  22. FROM t_at;# 1 2 3 5
  24. # 再次删除
  25. DELETE
  26. FROM t_at
  27. WHERE id = 5;
  29. # 若再次添加自增 按实验逻辑 将会出现 1 2 3 6
  31. # 重启MySQL服务
  33. # 重新添加 自增值
  34. INSERT t_at VALUES (0);
  35. # 查询
  36. SELECT *
  37. FROM t_at;# 结果 1 2 3 4 ,按理论来说是 1 2 3 6

结论:
说明 这个字段的自增值是以缓存形式保存的（内存中断电丢失） ,
但是在MySQL8.0之后,字段的自增值就不是在内存中缓存了(存在于重做日志中),所以重启并不会英雄原有自增值 结果1 2 3 6

FOREIGN KEY 外键

  1. 限定字段的引用完整性
  2. 主表（父表）：被引用的表，被参考的表
  3. 从表（子表）：引用别人的表，参考别人的表
  4. 从表的外键列，必须引用/参考主表的键主键或唯一约束的列为什么？因为被依赖/被参考的值必须是唯一的
  5. 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1；) ，也可以指定外键约束名。
  6. 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
  7. 删表时，先删从表（或先删除外键约束），再删除主表
  8. 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
  9. 在”从表”中指定外键约束，并且一个表可以建立多个外键约束
  10. 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename (errno: 150)”。例如：都是表示部门编号，都是int类型
  11. 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。（根据外键查询效率很高）
    删除外键约束后，必须手动删除对应的索引

    使用

    ```sql

    建立主表

    CREATE TABLE t_dept( id INT, name VARCHAR(25) );

建立从表

CREATE TABLE t_emp( id INT PRIMARY KEY AUTO_INCREMENT, dept_id INT, name VARCHAR(25), CONSTRAINT FOREIGN KEY dept_id_res_dept (dept_id) REFERENCES t_dept(id) );

报错 Failed to add the foreign key constraint. Missing index for constraint ‘t_emp_ibfk_1’ in the referenced table ‘t_dept’

主表id改为主键

ALTER TABLE t_dept MODIFY id INT PRIMARY KEY AUTO_INCREMENT;

再次执行建立从表 建立成功

添加约束

ALTER TABLE t_dept ADD CONSTRAINT FOREIGN KEY (id) REFERENCES t_at (id);

  1. <a name="Bthk5"></a>
  2. ### 删除约束
  3. ```sql
  4. ## 删除外键
  5. ALTER TABLE t_atat DROP FOREIGN KEY t_atat_ibfk_1;
  7. ## 外键自动创建了索引 也需要删除 默认字段名
  8. ALTER TABLE t_atat DROP INDEX dept_id;

约束生效

  1. INSERT INTO t_emp(id, name)
  2. VALUES (1, 'xx'); # 填 NULL 可以成功
  4. SELECT *
  5. FROM t_emp;
  8. # 失败 Cannot add or update a child row: a foreign key constraint fails (`atguigudb`.`t_emp`, CONSTRAINT `t_emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `t_dept` (`id`))
  9. INSERT INTO t_emp(id, dept_id, name)
  10. VALUES (2, 1, 'aa'); # 主表中没有 id = 1的数据行
  11. # 先执行
  12. INSERT INTO t_dept (id, name) VALUES (1, 'lih');
  13. # 重新添加 执行成功
  15. # 此时删除主表数据 Cannot delete or update a parent row: a foreign key constraint fails (`atguigudb`.`t_emp`, CONSTRAINT `t_emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `t_dept` (`id`))
  16. DELETE
  17. FROM t_dept
  18. WHERE id = 1;
  19. # 如果想删除 则需要先删除子表与其关联的所以数据才能删除主表数据

约束等级

  1. Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
  2. Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
  3. No action方式：如果子表中有匹配的记录，则不允许对父表对应候选健进行update/delete操作[默认]
  4. Restrict方式：同no action， 都是立即检查外键约束[默认]
  5. Set default方式（在可视化工具sQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
  6. 如果没有指定等级，就相当于Restrict方式。
  7. 对于外键约束，最好是采用：ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

    使用

    ```sql CREATE TABLE t_atat( id INT , dept_id INT, CONSTRAINT FOREIGN KEY (dept_id) REFERENCES t_dept (id) ON UPDATE CASCADE ON DELETE RESTRICT

    创建外键 并 指定 主表修改修改 主表删除时的 约束等级

    );

推荐使用 ON UPDATE CASCADE ON DELETE RESTRICT

  1. <a name="ZyJO8"></a>
  2. ### 【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
  3. 说明：<br />（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的<br />student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于 单机低并发，不适合<br />分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的指入速度。
  5. <a name="fSRHv"></a>
  6. ## CHECK 检查约束
  7. MySQL5.7及之前不支持 写了也不生效<br />为字段添加自定义检查限制
  8. <a name="x1TU8"></a>
  9. ### 使用 
  10. ```sql
  11. # 创建时使用
  12. CREATE TABLE t_ck(
  13.     id INT,
  14.     age INT CHECK ( age >= 0 AND age <= 160 ),
  15.     sex VARCHAR(20) CHECK ( sex = '男' OR sex = '女')
  16. );
  18. # 修改
  19. ALTER TABLE t_ck MODIFY id INT CHECK ( id <> NULL );

删除约束

  1. ALTER TABLE t_ck MODIFY id INT;

约束生效

  1. # Check constraint 't_ck_chk_1' is violated.
  2. INSERT INTO t_ck(id, age, sex)
  3. VALUES (1, -1, '未知');
  5. # Check constraint 't_ck_chk_2' is violated.
  6. INSERT INTO t_ck(id, age, sex)
  7. VALUES (1, 1, '男x');
  9. # 添加成功
  10. INSERT INTO t_ck(id, age, sex)
  11. VALUES (1, 1, '男');

DEFAULT 默认值

为某一列指定默认值 当此字段没有指定值(指定NULL也是指定了)时<未显式赋值> 使用默认值填充

使用

  1. # 创建时使用
  2. CREATE TABLE t_def(
  3.     id INT,
  4.     sex VARCHAR(20) DEFAULT '男'
  5. );
  7. # 修改时使用
  8. ALTER TABLE t_def MODIFY COLUMN id INT DEFAULT 1;

删除约束

  1. ALTER TABLE t_def MODIFY COLUMN id INT;

约束生效

  1. # 未指定sex值
  2. INSERT INTO t_def(id) VALUES (1);
  3. # 默认使用 男
  4. SELECT *
  5. FROM t_def; # id = 1 ,sex = 男
  7. # 指定值为 null
  8. INSERT INTO t_def(id, sex)
  9. VALUES (2, null);
  10. # 未使用默认值
  11. SELECT *
  12. FROM t_def;# id = 1 ,sex = NULL

问题

1、为什么建表时，加not null default “或 default O
答：不想让表中出现null值。

2、为什么不想要null 的值
答:（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。
(2) 效率不高。影响提高索引效果。因此，我们往往在建表时 not null default “ 或 default O

3、带AUTO_INCREMENT约束的字段值是从1开始的吗？
在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，
还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，
则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。
CREATE TABLE ttt (
id int NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COMMENT=’测试表’;
AUTO_INCREMENT=45 制定了自增值从45开始

4、并不是每个表都可以任意选择存储引擎？
外键约束（FOREIGN KEY）不能跨引擎使用。MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据
的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。