修改外键约束

最新推荐文章于 2025-04-17 10:39:57 发布

原创最新推荐文章于 2025-04-17 10:39:57 发布 · 720 阅读

2 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

6 篇文章

订阅专栏

参考：

https://blog.youkuaiyun.com/peng_666666/article/details/54813098

mysql> show create table in_community_line;

| in_community_line | CREATE TABLE `in_community_line` (
  `uuid` char(32) NOT NULL,
  `name` varchar(50) NOT NULL,
  `lft` int(10) unsigned NOT NULL,
  `rght` int(10) unsigned NOT NULL,
  `tree_id` int(10) unsigned NOT NULL,
  `level` int(10) unsigned NOT NULL,
  `parent_id` char(32) DEFAULT NULL,
  `room_id` char(32) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `in_community_line_room_id_0aa1b1cd_fk_common_room_uuid` (`room_id`),
  KEY `in_community_line_lft_7fd98161` (`lft`),
  KEY `in_community_line_rght_d2de5471` (`rght`),
  KEY `in_community_line_tree_id_247cf493` (`tree_id`),
  KEY `in_community_line_level_772a9cbc` (`level`),
  KEY `icl_parent_id_1211_pk_icl_uuid` (`parent_id`),
  CONSTRAINT `icl_parent_id_1211_pk_icl_uuid` FOREIGN KEY (`parent_id`) REFERENCES `in_community_line` (`uuid`),
  CONSTRAINT `in_community_line_room_id_0aa1b1cd_fk_common_room_uuid` FOREIGN KEY (`room_id`) REFERENCES `common_room` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

删除外键约束

mysql> ALTER TABLE in_community_line DROP FOREIGN KEY icl_parent_id_1211_pk_icl_uuid;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

查看已删除外键约束icl_parent_id_1211_pk_icl_uuid

| in_community_line | CREATE TABLE `in_community_line` (
  `uuid` char(32) NOT NULL,
  `name` varchar(50) NOT NULL,
  `lft` int(10) unsigned NOT NULL,
  `rght` int(10) unsigned NOT NULL,
  `tree_id` int(10) unsigned NOT NULL,
  `level` int(10) unsigned NOT NULL,
  `parent_id` char(32) DEFAULT NULL,
  `room_id` char(32) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `in_community_line_room_id_0aa1b1cd_fk_common_room_uuid` (`room_id`),
  KEY `in_community_line_lft_7fd98161` (`lft`),
  KEY `in_community_line_rght_d2de5471` (`rght`),
  KEY `in_community_line_tree_id_247cf493` (`tree_id`),
  KEY `in_community_line_level_772a9cbc` (`level`),
  KEY `icl_parent_id_1211_pk_icl_uuid` (`parent_id`),
  CONSTRAINT `in_community_line_room_id_0aa1b1cd_fk_common_room_uuid` FOREIGN KEY (`room_id`) REFERENCES `common_room` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

删除索引

mysql> ALTER TABLE in_community_line DROP INDEX icl_parent_id_1211_pk_icl_uuid;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

查看已删除外键约束icl_parent_id_1211_pk_icl_uuid

| in_community_line | CREATE TABLE `in_community_line` (
  `uuid` char(32) NOT NULL,
  `name` varchar(50) NOT NULL,
  `lft` int(10) unsigned NOT NULL,
  `rght` int(10) unsigned NOT NULL,
  `tree_id` int(10) unsigned NOT NULL,
  `level` int(10) unsigned NOT NULL,
  `parent_id` char(32) DEFAULT NULL,
  `room_id` char(32) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `in_community_line_room_id_0aa1b1cd_fk_common_room_uuid` (`room_id`),
  KEY `in_community_line_lft_7fd98161` (`lft`),
  KEY `in_community_line_rght_d2de5471` (`rght`),
  KEY `in_community_line_tree_id_247cf493` (`tree_id`),
  KEY `in_community_line_level_772a9cbc` (`level`),
  CONSTRAINT `in_community_line_room_id_0aa1b1cd_fk_common_room_uuid` FOREIGN KEY (`room_id`) REFERENCES `common_room` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

添加外键约束 on delete

ALTER TABLE in_community_line ADD CONSTRAINT `in_community_line_parent_id_f2bfc602_fk_in_community_line_uuid` FOREIGN KEY (`parent_id`) REFERENCES `in_community_line` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE;

查看添加外键约束in_community_line_parent_id_f2bfc602_fk_in_community_line_uuid

| in_community_line | CREATE TABLE `in_community_line` (
  `uuid` char(32) NOT NULL,
  `name` varchar(50) NOT NULL,
  `lft` int(10) unsigned NOT NULL,
  `rght` int(10) unsigned NOT NULL,
  `tree_id` int(10) unsigned NOT NULL,
  `level` int(10) unsigned NOT NULL,
  `parent_id` char(32) DEFAULT NULL,
  `room_id` char(32) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `in_community_line_room_id_0aa1b1cd_fk_common_room_uuid` (`room_id`),
  KEY `in_community_line_lft_7fd98161` (`lft`),
  KEY `in_community_line_rght_d2de5471` (`rght`),
  KEY `in_community_line_tree_id_247cf493` (`tree_id`),
  KEY `in_community_line_level_772a9cbc` (`level`),
  KEY `in_community_line_parent_id_f2bfc602_fk_in_community_line_uuid` (`parent_id`),
  CONSTRAINT `in_community_line_parent_id_f2bfc602_fk_in_community_line_uuid` FOREIGN KEY (`parent_id`) REFERENCES `in_community_line` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `in_community_line_room_id_0aa1b1cd_fk_common_room_uuid` FOREIGN KEY (`room_id`) REFERENCES `common_room` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |