跨应用程序生命周期的可维护性和稳定性如何?大多数数据的生命周期都比使用它的应用程序长。关系和数据完整性非常重要，不能寄希望于下一个开发团队在应用程序代码中做出正确的处理。如果您没有使用过不尊重自然关系的脏数据的db，那么您将会使用。数据完整性的重要性将变得非常清楚。

在DB2中，如果使用mqt(物化查询表)，优化器需要外键约束才能为任何给定的查询选择正确的计划。由于元数据包含基数信息，优化器会大量使用元数据来决定是否使用MQT。

数据结构设计的一个好的原则是确保表或对象的每个属性都服从于一个很好理解的约束。这很重要，因为如果您或您的程序可以依赖数据库中的有效数据，那么就不太可能出现由坏数据引起的程序缺陷。您还可以花费更少的时间来编写处理错误条件的代码，并且更有可能预先编写错误处理代码。

在许多情况下，这些约束可以在编译时定义，在这种情况下，您可以编写一个筛选器来确保属性总是在范围内，或者尝试保存属性失败。

然而，在许多情况下，这些约束可以在运行时更改。例如，您可能有一个“cars”表，其属性为“colour”，初始值为“red”、“green”和“blue”。在程序执行期间，可以将有效的颜色添加到初始列表中，并且添加的新“汽车”可以采用最新颜色列表中的任何颜色。此外，您通常希望这个更新的颜色列表在程序重新启动后仍然有效。

To answer your question, it turns out that if you have a requirement for data constraint that can change at run-time, and those changes must survive a program restart, foreign keys are the simplest and most concise solution to the problem. The development cost is the addition of one table (e.g. "colours", a foreign key constraint to the "cars" table, and an index), and the run-time cost is the extra table lookup for the up-to-date colours to validate the data, and this run-time cost is usually mitigated by indexing and caching.

如果您不使用外键来满足这些需求，那么您必须编写软件来管理列表、查找有效的条目、将其保存到磁盘、如果列表很大的话有效地组织数据、确保对列表的任何更新都不会破坏列表文件、在有多个读取器和/或写入器的情况下提供对列表的串行访问，等等。例如，你需要实现大量的RDBMS功能。

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.

From my experience its always better to avoid using FKs in Database Critical Applications. I would not disagree with guys here who say FKs is a good practice but its not practical where the database is huge and has huge CRUD operations/sec. I can share without naming ... one of the biggest investment bank of doesn't have a single FK in databases. These constrains are handled by programmers while creating applications involving DB. The basic reason is when ever a new CRUD is done it has to effect multiple tables and verify for each inserts/updates, though this won't be a big issue for queries affecting single rows but it does create a huge latency when you deal with batch processing which any big bank has to do as daily tasks.

最好避免fk，但它的风险必须由程序员来处理。

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

亚历克斯