本文将深入探讨MySQL中主键和外键的语法,包括如何创建、删除以及使用这些键,并通过实例展示其在实际应用中的强大功能
一、主键(Primary Key) 主键是表中的一个或多个字段,用于唯一标识表中的每一行数据
主键的值必须是唯一的,且不能为NULL
在MySQL中,创建主键的语法主要有两种形式: 1.在创建表时直接定义主键: sql CREATE TABLE 表名( 字段名1 数据类型 PRIMARY KEY, 字段名2 数据类型, ... ); 或者,如果主键由多个字段组成(即复合主键),则使用: sql CREATE TABLE 表名( 字段名1 数据类型, 字段名2 数据类型, ... PRIMARY KEY(字段名1,字段名2,...) ); 2.在已有表中添加主键: 如果表已经存在,但尚未定义主键,可以使用`ALTER TABLE`语句添加主键: sql ALTER TABLE 表名 ADD PRIMARY KEY(字段名); 对于复合主键,同样适用: sql ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2,...); 注意事项: - 一个表中只能有一个主键
- 主键字段的值必须唯一且不能为NULL
- 主键通常与`AUTO_INCREMENT`约束一起使用,以实现自动递增的唯一标识符
二、外键(Foreign Key) 外键是表中的一个字段或字段集合,它引用另一个表的主键
外键用于建立和强制执行两个表之间的链接,从而确保数据的引用完整性和一致性
在MySQL中,创建外键的语法如下: 1.在创建表时定义外键: 虽然MySQL允许在创建表时直接定义外键,但通常的做法是先创建表,然后再使用`ALTER TABLE`语句添加外键
不过,为了完整性,这里还是展示一下在创建表时定义外键的语法: sql CREATE TABLE 子表名( 字段名1 数据类型, 外键字段名 数据类型, ... FOREIGN KEY(外键字段名) REFERENCES父表名(主键字段名) 【ON DELETE{RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}】 【ON UPDATE{RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}】 ); 2.在已有表中添加外键: 更常见的做法是使用`ALTER TABLE`语句在已有表中添加外键: sql ALTER TABLE 子表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES父表名(主键字段名) 【ON DELETE{RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}】 【ON UPDATE{RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}】; 参数说明: -`ON DELETE`和`ON UPDATE`子句用于指定当父表中的行被删除或更新时,子表中相应行的处理策略
-`RESTRICT`:阻止删除或更新操作(默认值)
-`CASCADE`:级联删除或更新子表中的相关行
-`SET NULL`:将子表中的外键字段设置为NULL
-`NO ACTION`:不执行任何操作(但在严格模式下可能会引发错误)
-`SET DEFAULT`:将子表中的外键字段设置为默认值(MySQL中通常不支持此选项)
注意事项: - 外键字段的数据类型必须与父表的主键字段的数据类型相匹配
- 两个表必须是InnoDB表,因为MyISAM表不支持外键
- 外键字段必须建立了索引(MySQL4.1.2以后的版本在建立外键时会自动创建索引)
三、实例演示 为了更直观地理解主键和外键的用法,以下通过实例进行演示
示例表结构: 假设我们有两个表:`customers`(存储客户信息)和`orders`(存储订单信息)
`customers`表中的`customer_id`是主键,而`orders`表中的`customer_id`是外键,引用`customers`表中的`customer_id`
sql -- 创建customers表 CREATE TABLE customers( customer_id INT PRIMARY KEY, customer_name VARCHAR(255) NOT NULL ); -- 创建orders表 CREATE TABLE orders( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY(customer_id) REFERENCES customers(customer_id) ); 插入数据: 在向`orders`表中插入数据之前,必须确保`customers`表中已经存在相应的`customer_id`
sql -- 向customers表中插入数据 INSERT INTO customers(customer_id, customer_name) VALUES(1, Alice); INSERT INTO customers(customer_id, customer_name) VALUES(2, Bob); -- 向orders表中插入数据 INSERT INTO orders(order_id, customer_id, order_date) VALUES(1,1, 2023-10-01); INSERT INTO orders(order_id, customer_id, order_date) VALUES(2,2, 2023-10-02); 尝试违反外键约束: 如果尝试向`orders`表中插入一个不存在的`customer_id`,或者尝试修改`orders`表中的`customer_id`为一个不存在的值,将会导致外键约束错误
sql --尝试插入一个不存在的customer_id(将失败) INSERT INTO orders(order_id, customer_id, order_date) VALUES(3,3, 2023-10-03); -- ERROR1452(23000): Cannot add or update a child row: a foreign key constraint fails --尝试修改orders表中的customer_id为一个不存在的值(将失败) UPDATE orders SET customer_id =3 WHERE order_id =1; -- ERROR1452(23000): Cannot add or update a child row: a foreign key constraint fails 删除外键约束: 如果出于某种原因需要删除外键约束,可以使用`ALTER TABLE`语句
sql ALTER TABLE orders DROP