摘要:本文讲解了MySQL删除没有被关联的数据表和删除被其他表关联的主表的相关操作,希望对您学习MySQL数据库有所帮助。
1. 删除没有被关联的数据表
mysql> show tables; +-------------------+| Tables_in_test_db | +-------------------+| tb_dept1 || tb_dept2 || tb_emp3 || tb_emp4 || tb_emp5 || tb_emp6 || tb_emp8 || tb_empl || tb_employ2 | +-------------------+9 rows in set (0.00 sec) mysql> DROP TABLE IF EXISTS tb_emp5,emp6; Query OK, 0 rows affected, 1 warning (0.22 sec) mysql> show tables; +-------------------+| Tables_in_test_db | +-------------------+| tb_dept1 || tb_dept2 || tb_emp3 || tb_emp4 || tb_emp6 || tb_emp8 || tb_empl || tb_employ2 | +-------------------+8 rows in set (0.00 sec) mysql>
2. 删除被其他表关联的主表
mysql> CREATE TABLE tb_dept2 -> ( -> id INT(11) PRIMARY KEY, -> name VARCHAR(22), -> location VARCHAR(50) -> ); Query OK, 0 rows affected (0.40 sec) mysql> CREATE TABLE tb_emp -> ( -> id INT(11) PRIMARY KEY, -> name VARCHAR(25), -> deptId INT(11), -> salary FLOAT, -> CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept2(id) -> ); Query OK, 0 rows affected (0.30 sec) mysql> show create table tb_emp; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_emp | CREATE TABLE `tb_emp` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `deptId` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_emp_dept` (`deptId`), CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept2` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) mysql> drop table tb_dept2; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> mysql> alter table tb_emp drop foreign key fk_emp_dept; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop table tb_dept2; Query OK, 0 rows affected (0.09 sec) mysql>
本文由职坐标整理并发布,了解更多内容,请关注职坐标数据库MySQL频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号