Quite often we receive the errors with FK constraints Cannot add or update a child row: a foreign key constraint fails Suppose there are two tables inventory_source and contract_lines, and we are referring inventory_source_id in contract_lines from inventory_source and suppose we want to delete record from inventory_source and the record is already present in contract_lines or we want to delete the PK column from Base table, we get errors for FK constraints, we can avoid it using the steps jotted below.

CREATE TABLE inventory_source (
inventory_source_id int(11) NOT NULL AUTO_INCREMENT,
display_name varchar(40) NOT NULL,
state_id int(11) NOT NULL,
PRIMARY KEY (inventory_source_id),
KEY state_id (state_id),
CONSTRAINT ba_inventory_source_state_fk FOREIGN KEY (state_id) REFERENCES   ba_state (state_id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE contract_lines(
contract_line_id int(11) NOT NULL AUTO_INCREMENT,
inventory_source_id int(11) NULL ,
PRIMARY KEY (contract_line_id),
UNIQUE KEY contract_line_id (contract_line_id),
KEY AI_contract_line_id (contract_line_id),
KEY contract_lines_inventory_source_fk (inventory_source_id),
CONSTRAINT contract_lines_inventory_source_fk FOREIGN KEY       (inventory_source_id) REFERENCES ba_inventory_source (inventory_source_id)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=utf8 ;

我们可以采用以下步骤克服它:-

Delete or update the row from the inventory_source will automatically delete or update the matching rows in the contract_lines table and this is known as cascade delete or update. Another way of doing it is setting the column i.e inventory_source_id in the contract_lines table to NULL, when a record corresponding to it is deleted in the inventory_source table. We can restrict the parent table for delete or update in other words one can reject the delete or update operation for the inventory_source table. Attempt to delete or update a primary key value will not be permitted to proceed if there is a related foreign key value in the referenced table.

澄清数据库是一个没有主键或外键的商业数据库示例。

http://www.geekinterview.com/question_details/18869

有趣的是，技术文档花了很大的篇幅来解释表是如何关联的，用什么列来连接它们等等。

换句话说，它们本可以使用显式声明(DRI)连接表，但它们选择不这样做。

因此，澄清数据库充满了不一致，性能不佳。

但我认为它使开发人员的工作更容易，不必编写代码来处理引用完整性，例如在删除或添加之前检查相关行。

我认为，这就是关系数据库中没有外键约束的主要好处。它使开发变得更容易，至少从不顾一切的角度来看是这样。

Quite often we receive the errors with FK constraints Cannot add or update a child row: a foreign key constraint fails Suppose there are two tables inventory_source and contract_lines, and we are referring inventory_source_id in contract_lines from inventory_source and suppose we want to delete record from inventory_source and the record is already present in contract_lines or we want to delete the PK column from Base table, we get errors for FK constraints, we can avoid it using the steps jotted below.

CREATE TABLE inventory_source (
inventory_source_id int(11) NOT NULL AUTO_INCREMENT,
display_name varchar(40) NOT NULL,
state_id int(11) NOT NULL,
PRIMARY KEY (inventory_source_id),
KEY state_id (state_id),
CONSTRAINT ba_inventory_source_state_fk FOREIGN KEY (state_id) REFERENCES   ba_state (state_id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE contract_lines(
contract_line_id int(11) NOT NULL AUTO_INCREMENT,
inventory_source_id int(11) NULL ,
PRIMARY KEY (contract_line_id),
UNIQUE KEY contract_line_id (contract_line_id),
KEY AI_contract_line_id (contract_line_id),
KEY contract_lines_inventory_source_fk (inventory_source_id),
CONSTRAINT contract_lines_inventory_source_fk FOREIGN KEY       (inventory_source_id) REFERENCES ba_inventory_source (inventory_source_id)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=utf8 ;

我们可以采用以下步骤克服它:-

Delete or update the row from the inventory_source will automatically delete or update the matching rows in the contract_lines table and this is known as cascade delete or update. Another way of doing it is setting the column i.e inventory_source_id in the contract_lines table to NULL, when a record corresponding to it is deleted in the inventory_source table. We can restrict the parent table for delete or update in other words one can reject the delete or update operation for the inventory_source table. Attempt to delete or update a primary key value will not be permitted to proceed if there is a related foreign key value in the referenced table.

我可以看到使用外键的一些原因(有人提到，孤立行很烦人)，但我也从不使用它们。对于一个相对正常的DB模式，我不认为它们是100%需要的。约束是好的，但我认为通过软件来实施约束是更好的方法。

亚历克斯

@ emphasis——这正是导致维护噩梦的心态。

为什么，哦，为什么要忽略声明性引用完整性(其中数据至少可以保证一致)，而支持所谓的“软件强制”，这充其量是一种薄弱的预防措施。

我经常使用它们，但后来我为金融系统制作数据库。数据库是应用程序的关键部分。如果金融数据库中的数据不是完全准确的，那么无论你在代码/前端设计上投入多少精力都没有意义。你只是在浪费时间。

还有一个事实是，多个系统通常需要直接与数据库接口-从其他系统只是读取数据(Crystal Reports)到系统插入数据(不一定使用我设计的API;它可能是由一个刚刚发现VBScript并拥有SQL框SA密码的愚蠢的经理编写的)。如果数据库不能像白痴一样证明它可能是，好吧，再见数据库。

如果您的数据很重要，那么可以使用外键，创建一套存储过程来与数据交互，并创建最强大的DB。如果您的数据不重要，那么为什么要开始创建数据库呢?