使用外键的原因:

you won't get Orphaned Rows you can get nice "on delete cascade" behavior, automatically cleaning up tables knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality. FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance they enable all kinds of auto-generated support -- ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc. someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented

不使用外键的原因:

you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn by enforcing relationships, FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want. (Granted, in such cases, what you are trying to do is create an Orphaned Row, and that's not usually a good thing). This is especially painful when you are doing large batch updates, and you load up one table before another, with the second table creating consistent state (but should you be doing that sort of thing if there is a possibility that the second load fails and your database is now inconsistent?). sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it you are just being lazy :-)

我认为(我不确定!)大多数已建立的数据库都提供了一种指定外键的方法，这种方法不是强制的，只是一些元数据。由于不强制执行消除了不使用fk的所有理由，如果第二部分中的任何理由适用，您可能应该走那条路。

我一直认为不用它们是懒惰的表现。我被教导应该一直这样做。但后来，我没有听乔尔的讨论。他也许有充分的理由，我不知道。

外键对于任何关系数据库模型都是必不可少的。

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.

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

One time when an FK might cause you a problem is when you have historical data that references the key (in a lookup table) even though you no longer want the key available. Obviously the solution is to design things better up front, but I am thinking of real world situations here where you don't always have control of the full solution. For example: perhaps you have a look up table customer_type that lists different types of customers - lets say you need to remove a certain customer type, but (due to business restraints) aren't able to update the client software, and nobody invisaged this situation when developing the software, the fact that it is a foreign key in some other table may prevent you from removing the row even though you know the historical data that references it is irrelevant. After being burnt with this a few times you probably lean away from db enforcement of relationships. (I'm not saying this is good - just giving a reason why you may decide to avoid FKs and db contraints in general)