MySQL 添加外键时的注意事项

最新推荐文章于 2023-11-12 17:21:17 发布

梦太难醒

最新推荐文章于 2023-11-12 17:21:17 发布

阅读量753

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/TrapYourCage/article/details/114645363

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL 添加外键时的注意事项

1.数据库引擎： InnoDB
2.一表A字段做为二表的外键时。A字段的长度需要和二表的B字段长度一致。
3.A字段需要是一表的主键

MySQL语句

添加外键的SQL语句：
CONSTRAINT picture_number FOREIGN KEY (picture_number) REFERENCES game_table (game_number) ON DELETE CASCADE ON UPDATE CASCADE

创建 game_table 表和 game_picture_table 表：

CREATE TABLE `game_table`  (
  `game_id` int(11) NOT NULL AUTO_INCREMENT,
  `game_number` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '游戏编号',
  `game_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `game_details` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '游戏详情',
  PRIMARY KEY (`game_id`, `game_number`) USING BTREE,
  INDEX `game_number`(`game_number`) USING BTREE,
  INDEX `game_id`(`game_id`) USING BTREE,
  INDEX `game_name`(`game_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;


CREATE TABLE `game_picture_table`  (
  `picture_id` int(11) NOT NULL AUTO_INCREMENT,
  `picture_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '地址',
  `picture_number` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`picture_id`) USING BTREE,
  INDEX `picture_number`(`picture_number`) USING BTREE,
  CONSTRAINT `picture_number` FOREIGN KEY (`picture_number`) REFERENCES `game_table` (`game_number`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

结果如下：

在这里插入图片描述

在这里插入图片描述
添加外键时设置的四个属性的含义：

CASCADE
在父表上update/delete记录时，子表对应的记录同步更新 
 
SET NULL
在父表上update/delete记录时，将子表上对应的记录设为null  
 
NO ACTION
如果子表中匹配了父表的记录,则父表的记录不能进行update/delete操作  
 
RESTRICT
同no action, 都是先检查外键约束