MySQL约束与索引详解及建表实操

数据库设计与数据完整性基础

关系型数据库的设计通常遵循实体-关系（ER）模型和三范式原则。ER模型中，E (Entity) 代表实体，对应数据库中的一张表；R (Relationship) 代表关系。设计时需确保：

• 第一范式：列不可拆分，确保原子性。
• 第二范式：确保每行可唯一标识。
• 第三范式：消除传递依赖，通过主键进行关系引用。

在物理结构上，数据存储在库中的表内。表名具有唯一性，定义字段的数据类型、长度和名称，类似编程中的“类”；每一行记录则是具体的“对象”。良好的表结构设计是保证数据准确可靠的前提。

核心概念：约束与索引的作用机制

数据完整性旨在防止不符合语义的数据入库，分为四类：实体完整性（如无重复记录）、域完整性（如年龄范围）、引用完整性（如部门归属一致）和用户自定义完整性。

约束（Constraint）用于维护数据的业务规则，是逻辑对象，不额外占用存储空间。常见的约束包括主键、外键、唯一、非空和检查约束。

索引（Index）则是物理存储结构，类似字典目录，指向数据页。建立索引会增加存储空间及插入修改的时间开销，但能显著提升查询速度。应优先在经常用于查询、排序或范围查找的列上建立索引，避免在更新频繁或基数极低的列（如性别）上滥用索引。

第1步：查看现有约束与索引信息

目的：了解当前表结构状态，排查冲突或确认配置。

操作：

# 查看表的约束信息
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
或
SHOW CREATE TABLE 表名;

# 查看表的索引信息
SHOW INDEX FROM 表名称;

第2步：设置主键约束（Primary Key）

目的：确立表中每条记录的唯一定位符。

参数：单列主键或复合主键（不建议使用，易违背范式）。

操作：

# 单个字段设置主键（定义时）
create table t_user(
	id int primary key,
    username varchar(20),
    password varchar(20)
);

# 多个字段设置联合主键
create table t_user(
	id int,
    username varchar(20),
    password varchar(20),
    primary key(id,username)
);

# 建表后添加主键
alter table 表名称 add primary key (主键字段列表);

特性：值必须唯一且非空。创建主键会自动生成对应的索引，删除主键则索引同步移除。

第3步：应用自增约束（Auto Increment）

目的：自动生成唯一递增ID。

条件：字段必须为整型、建过键（通常为主键或唯一键）且非空。一张表仅允许一个自增字段。

操作：

create table t_user(
	id int primary key auto_increment,
    username varchar(20),
    password varchar(20)
);

# 建表后设置/删除自增属性需使用 MODIFY 关键字
alter table [数据库.]表名 modify 自增字段名 数据类型 auto_increment;

注意：插入数据时若该字段值为 null 或 0，将自动替换为下一个增长值。

第4步：配置唯一键约束（Unique Key）

目的：保证字段值唯一，但允许为空。

操作：

# 建表时定义
create table t_user(
	id int primary key auto_increment,
    username varchar(20) unique key,
    unique key(username,password) -- 联合唯一
);

# 建表后增加唯一键（默认为字段名作为索引名）
alter table 表名称 add 【constraint 约束名】 unique key (字段名列表);

删除：唯一键通过删除对应索引移除：ALTER TABLE 表名称 DROP INDEX 唯一性约束名;

第5步：指定非空与默认值约束

非空约束（Not Null）确保字段必须有值。

# 建表后调整非空属性（注意保留原有的 default 值）
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL DEFAULT '默认值';

默认值约束（Default）在不赋值时提供缺省值。

create table t_user(
	gender char not null default '男'
);
-- 插入时使用 default 关键字或留空

第6步：建立外键约束（Foreign Key）

目的：维护表间参照完整性，常见于一对多或多对多关系中。

前置条件：主表必须存在，且被引用列为键列（主键或唯一键），两表字符集需兼容。

操作：

create table t_dept(
	id int primary key auto_increment,
    name varchar(20)
);

create table t_emp(
	id int primary key auto_increment,
    dept_id int,
    foreign key(dept_id) references t_dept(id) 
    on update cascade -- 可选的级联操作
);

# 建表后添加外键
alter table 从表名称 add constraint 外键名 foreign key (从表字段) references 主表(主表字段);

注意点：

• 删除外键需用 DROP FOREIGN KEY 外键名。
• 外键约束不会自动删除关联的普通索引，手动删除需使用 DROP INDEX。

总结

合理运用约束与索引是构建健壮MySQL数据库的关键。主键保障实体识别，唯一与非空约束确保数据质量，而外键维系表间关联。虽然索引带来存储与维护成本，但对高频查询列的科学布局能有效优化性能。以上就是MySQL约束与索引概念详解的详细内容，更多关于MySQL数据库设计与优化的资料请关注本站其它相关文章！