为什么外键在理论上比在实践中更多使用？ [英] Why are foreign keys more used in theory than in practice?

问题描述

当你学习关系理论时，外键当然是强制性的。但在实践中，在我工作的每个地方，表产品和联接总是通过在查询中显式指定键来完成，而不是依赖DBMS中的外键。

这样，你当然可以通过不是外键的字段来连接两个表，并产生意想不到的结果。

你认为这是为什么？应该不是DBMS强制的联接和产品只能由外键？

编辑：感谢所有的答案。现在我清楚的是，FK的主要原因是参考完整性。但是如果你设计一个DB，模型中的所有关系（在ERD中的I.E.箭头）成为外键，至少在理论上，无论你是否在DBMS中定义它们，它们在语义上是FK。我不能想象需要通过不是FK的字段连接表。 有人可以给出一个有意义的例子吗？

PS：我知道N：M关系变成单独的表，

解决方案

外键约束存在的原因是为了保证引用

外键标识在一个表中引用另一个表中的一列或一组列的一列或一组列。引用列的一行中的值必须出现在引用表中的单个行中。

因此，引用表中的一行不能包含不是存在于被引用的表中（除了潜在的NULL），这种方式可以引用链接信息在一起，它是数据库规范化的一个重要部分。 （维基百科）

---

RE：您的问题：我无法想象需要通过非FK的字段连接表：

定义外键约束时，引用表中的列必须是被引用表的主键，或至少是候选键。 p>

在连接时，不需要加入主键或候选键。

这可能是有意义的：

  CREATE TABLE clients（
 client_id uniqueidentifier NOT NULL，
 client_name nvarchar ）NOT NULL，
 client_country char（2）NOT NULL 
）; 
 
 CREATE TABLE供应商（
 supplier_id uniqueidentifier NOT NULL，
 supplier_name nvarchar（250）NOT NULL，
 supplier_country char（2）NOT NULL 
）; 
  

然后查询如下：

  SELECT 
 client_name，supplier_name，client_country 
 FROM 
客户
 INNER JOIN 
供应商ON（clients.client_country = suppliers.supplier_country） 
 ORDER BY 
 client_country; 
 

另一种情况是这些连接有意义的是提供地理空间功能的数据库，如SQL Server 2008或Postgres与PostGIS。您将能够执行以下查询：

  SELECT 
州，选民
 FROM 
选举人
 INNER JOIN 
 postcodes on（postcodes.Location.STIntersects（electorates.Location）= 1）; 
  

资料来源： ConceptDev - SQL Server 2008地理位置：STIntersects，STArea

另一个类似的地理空间示例在公认的答案 Sql 2008查询问题 - 哪个LatLong存在于地理多边形？：

  SELECT 
 G.Name，COUNT（CL.Id）
 FROM 
 GeoShapes G 
 INNER JOIN 
 CrimeLocations CL ON G.ShapeFile.STIntersects（CL.LatLong）= 1 
 GROUP BY 
 G.Name; 
  

这些都是与外键和候选键无关的有效SQL连接，在实践中是有用的。

When you study relational theory foreign keys are, of course, mandatory. But in practice, in every place I worked, table products and joins are always done by specifying the keys explicitly in the query, instead of relying on foreign keys in the DBMS.

This way, you could of course join two tables by fields that are not meant to be foreign keys, having unexpected results.

Why do you think that is? Shouldn't DBMSs enforce that Joins and Products be made only by foreign keys?

EDIT: Thanks for all the answers. It's clear to me now that the main reason for FKs is reference integrity. But if you design a DB, all relationships in the model (I.E. arrows in the ERD) become Foreign keys, at least in theory, whether or not you define them as such in your DBMS, they're semantically FKs. I can't imagine the need to join tables by fields that aren't FKs. Can someone give an example that makes sense?

PS: I'm aware about the fact that N:M relationships become separate tables and not foreign keys, just omitted it for simplicity's sake.

解决方案

The reason foreign key constraints exist is to guarantee that the referenced rows exist.

"The foreign key identifies a column or a set of columns in one table that refers to a column or set of columns in another table. The values in one row of the referencing columns must occur in a single row in the referenced table.

Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization." (Wikipedia)

---

RE: Your question: "I can't imagine the need to join tables by fields that aren't FKs":

When defining a Foreign Key constraint, the column(s) in the referencing table must be the primary key of the referenced table, or at least a candidate key.

When doing joins, there is no need to join with primary keys or candidate keys.

The following is an example that could make sense:

CREATE TABLE clients (
    client_id       uniqueidentifier  NOT NULL,
    client_name     nvarchar(250)     NOT NULL,
    client_country  char(2)           NOT NULL
);

CREATE TABLE suppliers (
    supplier_id       uniqueidentifier  NOT NULL,
    supplier_name     nvarchar(250)     NOT NULL,
    supplier_country  char(2)           NOT NULL
);

And then query as follows:

SELECT 
    client_name, supplier_name, client_country 
FROM 
    clients 
INNER JOIN
    suppliers ON (clients.client_country = suppliers.supplier_country)
ORDER BY
    client_country;

Another case where these joins make sense is in databases that offer geospatial features, like SQL Server 2008 or Postgres with PostGIS. You will be able to do queries like these:

SELECT
    state, electorate 
FROM 
    electorates 
INNER JOIN 
    postcodes on (postcodes.Location.STIntersects(electorates.Location) = 1);

Source: ConceptDev - SQL Server 2008 Geography: STIntersects, STArea

You can see another similar geospatial example in the accepted answer to the post "Sql 2008 query problem - which LatLong’s exists in a geography polygon?":

SELECT 
    G.Name, COUNT(CL.Id)
FROM
    GeoShapes G
INNER JOIN 
    CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1
GROUP BY 
    G.Name;

These are all valid SQL joins that have nothing to do with foreign keys and candidate keys, and can still be useful in practice.

这篇关于为什么外键在理论上比在实践中更多使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！