如何测试与外键链接的表? [英] How to test tables linked with foreign keys?

问题描述

我正在使用Redbean ORM使用mysql和codeigniter.为许多关联实现外键后，运行时出现以下错误:

drop TABLE IF EXISTS `temp`

Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails thrown

然后我将SHOW ENGINE INNODB STATUS输入到phpmyadmin中.输出包括:

LATEST FOREIGN KEY ERROR------------------------:  Cannot drop table `db1`.`temp`because it is referenced by `db1`.`temp_workers`.

换句话说，另一个表引用了FK.出于测试目的，我认为最好的办法是删除所有关联的表，并使用我正在测试的控制器重新创建它们.这是最好的方法吗?我尝试过:

drop TABLE IF EXISTS `temp` `temp_workers`

，但仍然出现上述错误，并且drop命令不起作用.另外:

truncate TABLE `temp`, `temp_workers`

给予:

You have an error in your SQL syntax

解决方案

如注释中所述，您必须将任何带有FK约束的表拖放到其他表中，然后再删除要链接到的表.

示例:

User
  id: 1
  name: Mike

Address 
  id: 1
  user_id: 1 (FK constraint to User.id table.column)
  address_1: 555 Main Street

此设置为1:1关系(有关数据规范化的更多信息)，其中一个用户行可以引用一个地址行，并且由于该地址行取决于用户行的存在，因此，如果尝试删除该用户行，则会看到提到的错误.

但是，如果您首先放下Address表，那么一切都将按预期工作，因为User表与其他任何表都不是FK.

确保架构中的参照完整性可确保您不会出现孤立行将渗透到整个数据驱动的应用程序中.

您还可以发出以下命令:

SET foreign_key_checks = 0;
# Do Stuff
SET foreign_key_checks = 1;

但是我强烈建议您这样做，因为这可能会破坏数据的参照完整性，并最终导致混乱.我见过有人在企业环境中执行此操作，并且花费了数周的时间对其进行清理.但是，如果您出于测试目的严格执行此操作；例如编写单元测试或只是学习，而又不想每次都丢掉表，您可以这样做:

# Because we remove the foreign key check, we can truncate in any order
SET foreign_key_checks = 0;
TRUNCATE TABLE user;
TRUNCATE TABLE address;
SET foreign_key_checks = 1;

使用外键约束进行正确的架构设计可以为任何数据驱动的应用程序奠定良好的基础.花时间了解何时使用以及如何构造外键约束，但是随着时间的流逝，您将开始理解.入门的好方法是下载一个开源项目，例如 magento ， vbulletin 和看一下他们的模式.您甚至可以使用 MySQL工作台内省这些模式，并查看其 解决方案

As mentioned in the comments you have to drop any tables with FK contraints to other tables, first, then you can drop the tables being linked to.

Example:

User
  id: 1
  name: Mike

Address 
  id: 1
  user_id: 1 (FK constraint to User.id table.column)
  address_1: 555 Main Street

This setup is a 1:1 relationship (more on data normalization), where one user row can reference one address row, and because the address row is dependent upon the existence of the user row, if you attempt to remove the user row, you will see the errors you mentioned.

But if you drop the Address table first, everything works as expected because the User table is not FK to any other table.

Ensuring referential integrity within your schema ensures you do not end up with orphaned rows, which will permeate throughout your data driven application.

You could also issue the following commands:

SET foreign_key_checks = 0;
# Do Stuff
SET foreign_key_checks = 1;

But I would strongly advise against this, as you could break the referential integrity of your data, and end up in a real mess. I've seen someone do this in an enterprise environment and it took them weeks to clean it up. However, if you are doing this STRICTLY for testing purposes; like writing unit tests, or just learning, and you didn't want to drop the tables every time, you could do this:

# Because we remove the foreign key check, we can truncate in any order
SET foreign_key_checks = 0;
TRUNCATE TABLE user;
TRUNCATE TABLE address;
SET foreign_key_checks = 1;

Proper schema design using foreign key constraints goes along way to building a good foundation for any data driven application. It will take time to get your head around when to use, and how to construct foreign key constraints, but over time you will begin to understand. A good way to get started is to download an open source project like magento, wordpress, or vbulletin and take a look at their schemas. You can even introspect these schemas using MySQL workbench and view their Entity-Relationship Diagrams (ERDs), which will visually demonstrate links between tables.

这篇关于如何测试与外键链接的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！