MySQL删除表的时候忽略外键约束

  删除表不是特别常用,特别是对于存在外键关联的表,删除更得小心。但是在开发过程中,发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情;另外在测试的时候,也有需要重新创建数据库的所有表。当然很多自动化工具也可以做这样的事情。

  删除表的时候有时会遇到这样的错误消息

  ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

  这是因为你尝试删除的表中的字段被用作了其他表的外键,因此在删除这个表(父表)之前必须先删除具有外键的表(子表)。也就是说,删除表的过程需要和创建表的过程一致。

  但是这往往不可接受,一方面如果表太多了,手动排序有点不可接受;另一方面,现在还没有自动的工具对进行排序(其实也不是不能实现)。因此,mysql中提供了一个变量FOREIGN_KEY_CHECKS来设置是否在必要的时候检查外键约束。一般比较推荐这样做:

  首先,自动生成所有的DROP语句,将其中的MyDatabaseName替换成你的数据库名称:

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';

  然后,在生成的代码前后添加下面设置FOREIGN_KEY_CHECKS变量的语句:

SET FOREIGN_KEY_CHECKS = 0
-- DROP语句
SET FOREIGN_KEY_CHECKS = 1;

  不过,要是忘记了最后一句也没太大关系,这个变量是基于Session的,也就是说,当你关闭了客户端,重新建立连接的时候,这个变量会恢复默认值。如果需要在全局范围内不检查外键约束(这种情况会比较少吧),可以这样做:

SET GLOBAL FOREIGN_KEY_CHECKS = 0;
或者
set @@global.FOREIGN_KEY_CHECKS = 0;
转载至:http://zhouliang.pro/2013/12/18/MySQL-drop-table-ignore-foreign-key-constraints

转载于:https://www.cnblogs.com/lojun/p/7456506.html

<think>好的,用户想知道如何在MySQL中创建包含约束的SQL结构。首先,我需要回忆一下的基本概念和语法。用于建立之间的关系,确保数据的参照完整性。用户可能已经了解基础的创建,但需要具体的约束步骤。 接下来,我应该考虑用户可能的实际应用场景。比如,他们可能有两个,比如部门和员工,员工需要引用部门的ID作为。这时候需要定义约束,确保员工中的部门ID存在于部门中。 然后,我需要查阅正确的MySQL语法。通常在CREATE TABLE语句中使用FOREIGN KEY关字,并指定REFERENCES指向父和对应的列。还要注意存储引擎必须是InnoDB,因为MyISAM不支持。用户可能忽略这一点,导致约束未生效,所以需要提醒他们检查引擎类型。 另,数据类型必须匹配,列和引用列的数据类型要一致,否则约束会失败。比如,如果父的列是INT,子列也必须是INT,不能是VARCHAR或其他类型。这也是常见的错误点,需要特别说明。 可能还需要提到约束的可选参数,比如ON DELETE和ON UPDATE的行为,比如CASCADE、SET NULL、RESTRICT等。用户可能想知道这些选项的作用,以便根据需求选择合适的行为。 还要考虑用户是否需要示例代码。给出具体的例子会更有帮助,比如创建部门和员工的SQL语句,展示如何添加约束。同时,提醒他们在创建后如何验证是否生效,比如使用SHOW CREATE TABLE命令查看结构。 可能还需要提到如何处理约束失败的情况,比如插入数据时违反约束MySQL会报错。这时候用户需要检查数据是否符合引用,或者是否临时关闭了检查。但关闭检查可能有风险,需谨慎操作,引用相关文档中的建议。 另,用户可能遇到约束未生效的问题,需要检查引擎、数据类型、索引是否存在等。比如,父的被引用列需要有索引,如果没有,约束可能无法正确创建。这也是常见问题之一,需要涵盖在回答中。 总结下来,回答的结构应该是先解释的作用,然后给出创建的示例,详细说明每个部分,再列出注意事项和常见问题。确保语法正确,使用中文,并且引用提供的参考资料中的相关部分,比如关闭检查的风险和存储引擎的要求。</think>### 如何在 MySQL 中创建包含约束的 SQL 结构 #### 约束的作用 约束(Foreign Key Constraint)用于确保两个之间的数据关联完整性。例如,部门(父)和员工(子)中,员工的部门 ID 必须存在于部门中,否则数据无效[^3][^4]。 #### 完整步骤及示例 1. **创建父(被引用)** 父需包含主或唯一约束字段,例如部门: ```sql CREATE TABLE t_dept ( id INT PRIMARY KEY, name VARCHAR(22) UNIQUE, location VARCHAR(50) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` - `id` 是主,`name` 通过 `UNIQUE` 保证唯一性[^4] - 必须使用 `InnoDB` 存储引擎(MyISAM 不支持) 2. **创建子(包含)** 在员工中定义约束: ```sql CREATE TABLE t_emp ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), dept_id INT, FOREIGN KEY (dept_id) REFERENCES t_dept(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` - `FOREIGN KEY (dept_id)` 指定字段 - `REFERENCES t_dept(id)` 指向父的主 - `ON DELETE CASCADE` 示父删除记录时自动删除关联记录 - `ON UPDATE CASCADE` 示父更新主时同步更新子[^5] #### 关注意事项 1. **存储引擎** 必须使用 `InnoDB`,否则约束不生效。 2. **数据类型一致性** 字段和父字段的数据类型必须完全一致(例如同为 `INT`)。 3. **索引要求** 父被引用的字段需有主或唯一索引,子字段会自动创建索引。 4. **约束行为选项** | 行为 | 作用 | |---------------|----------------------------------------------------------------------| | `CASCADE` | 父操作(删除/更新)时,子同步执行相同操作 | | `SET NULL` | 父操作时,子字段设为 `NULL`(需字段允许为 `NULL`) | | `RESTRICT` | 默认行为,阻止父的操作(若子存在关联记录) | | `NO ACTION` | 同 `RESTRICT` | #### 验证约束 通过 `SHOW CREATE TABLE` 查看结构: ```sql SHOW CREATE TABLE t_emp; ``` 输出中应包含 `CONSTRAINT` 相关定义[^5]。 #### 常见问题解决 - **未生效**:检查存储引擎、字段类型、父索引[^3]。 - **临时关闭约束**(慎用): ```sql SET foreign_key_checks = 0; -- 关闭约束检查[^2] -- 执行数据操作 SET foreign_key_checks = 1; -- 重新启用约束 ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值