MySQL数据库五大约束详解：主键、非空、唯一、外键与默认约束

数据库约束的概念与作用

约束是对表中数据进行限定的规则，旨在确保数据的正确性、有效性和完整性。在MySQL中，常见的约束分为以下五类：

• 主键约束 (PRIMARY KEY)：规定字段必须非空且唯一。一张表只能有一个主键，它是表中记录的唯一标识。
• 非空约束 (NOT NULL)：确保字段的值不能为空。
• 唯一约束 (UNIQUE)：保证字段值的唯一性，但允许为NULL。
• 外键约束 (FOREIGN KEY)：一个表中的字段引用另一个表的主键或唯一键，用于建立表与表之间的关联关系。
• 默认约束 (DEFAULT)：为字段指定默认值，当插入记录未提供该字段值时，自动填入默认值（字符串类型的默认值需使用引号）。

五大约束的添加与删除操作

在实际开发中，我们通常需要在建表时直接定义约束，或在已有表结构上通过 ALTER TABLE 语句进行调整。以下是各种约束的添加与删除方法总结。

第1步：理解添加约束的基础语法

添加约束主要分为“列级约束”和“表级约束”，也可以通过修改表结构来添加。具体方法如下：

1. 建表时添加列级约束：
   直接在列定义后追加约束关键字。
   CREATE TABLE 表名(列名 数据类型 约束);
2. 建表时添加表级约束：
   在所有列定义结束后，单独定义约束。适用于主键、唯一和外键。
   CREATE TABLE 表名(..., CONSTRAINT 别名 约束类型(字段名));
3. 通过修改列名添加约束：
   ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新类型 约束;
4. 通过修改列属性添加约束：
   ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 约束;
5. 添加新列时带约束：
   ALTER TABLE 表名 ADD COLUMN 新列名 类型 约束;
6. 外键特有的添加方式：
   ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(字段) REFERENCES 主表(主表字段);

第2步：掌握删除约束的方法

删除约束主要有两种思路：一是通过修改列定义去掉约束关键字；二是使用专门的 DROP 命令。

• 修改列去掉约束：
  在变更列名或列类型时，不包含原来的约束关键字即可实现删除。
  ALTER TABLE 表名 CHANGE/MODIFY COLUMN 列名 类型;
• 使用 DROP 命令精准删除：
  ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

如果不确定约束的名字，可以通过以下命令查看表中所有索引和约束信息：

SHOW INDEX FROM 表名;

第3步：五大约束对应的增删策略对照

不同约束在添加和删除时支持的语法有所不同，具体对应关系如下：

• 非空约束 (NOT NULL)
  添加：支持在建表（列级/表级）、改列名、改类型、加新列时使用。
  删除：可通过修改列名或修改列类型时移除关键字来删除。
• 唯一约束 (UNIQUE)
  添加：支持上述所有添加方式。
  删除：可通过修改列操作删除，或使用 DROP INDEX 索引名 删除。
• 默认约束 (DEFAULT)
  添加：支持上述所有添加方式。
  删除：可通过修改列名或修改列类型时移除关键字来删除。
• 主键约束 (PRIMARY KEY)
  添加：支持上述所有添加方式。
  删除：建议使用 DROP PRIMARY KEY，因为每个表只能有一个主键，无需指定名字。
• 外键约束 (FOREIGN KEY)
  添加：推荐使用建表表级约束或单独的 ADD CONSTRAINT 语句。
  删除：必须使用 DROP FOREIGN KEY 外键名。

外键约束实战：消除数据冗余

外键的主要作用是维护参照完整性，防止出现无效的数据引用。下面通过一个员工管理系统的案例进行说明。

第4步：分析冗余问题

假设我们有一个包含员工信息的表，字段包括：员工编号、姓名、年龄、所属部门、部门所在地。由于同一部门的地址相同，这会导致大量的数据冗余。例如，“研发部-广州”会在每条研发部员工的记录中重复出现。

第5步：拆分表格以优化设计

为了解决冗余，我们将数据拆分为两张表：员工表（存储人员信息）和部门表（存储部门基础信息）。两张表通过共同的ID字段关联。

在这两个表中，id 字段均作为主键。

第6步：引入外键限制数据异常

如果没有外键保护，用户可以随意删除部门表中的记录，导致员工表中留下指向不存在部门的“孤儿数据”。或者，可以在员工表中录入不存在的部门ID。为了避免这些问题，我们需要在员工表中对 dep_id 字段添加外键约束，使其引用部门表的 id。

在创建员工表时，可以使用以下表级约束语法定义外键：

CREATE TABLE employee(
    id INT PRIMARY KEY,
    sname VARCHAR(5),
    age INT,
    dep_id INT,
    CONSTRAINT aaa FOREIGN KEY(dep_id) REFERENCES department(id)
);

添加外键后，系统将强制执行以下规则：

1. 禁止删除被引用的主表记录：只要员工表中存在 dep_id=1 的记录，就无法删除部门表中 id=1 的部门。
2. 禁止插入非法的从表记录：向员工表插入数据时，dep_id 必须是部门表中已存在的 ID，否则插入失败。

自增长列 (AUTO_INCREMENT) 的应用

对于数值类型的字段，通常希望其能自动递增，以避免手动输入错误或重复。在MySQL中，可以使用 AUTO_INCREMENT 属性来实现。

第7步：创建自增长主键

自增长通常与主键结合使用。在定义列时，只需在主键后添加 AUTO_INCREMENT 关键字。

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sname CHAR(4)
);

第8步：验证自增长效果

当表中已有数据时，再次插入新记录，若将自增列设为 NULL 或不写该字段，MySQL会自动填入比当前最大值大1的数字。

INSERT INTO student VALUES(NULL, '李四');

第9步：修改与重置自增长设置

添加自增长：若表已存在，可通过修改列属性添加：
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 AUTO_INCREMENT;

删除自增长：修改列属性时去掉该关键字即可。
ALTER TABLE 表名 MODIFY COLUMN 列名 类型;

设置自增长步长：
MySQL默认的起始值为1，步长为1。起始值不可更改，但步长可以设置。通过以下命令查看当前变量名并设置全局或会话级别的步长：

SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment = 步长数值;

以上就是MySQL学习之数据库表五大约束详解小白篇的详细内容，更多关于MySQL五大约束的资料请关注本站其它相关文章！