SQL 当我们可以在没有它的情况下加入时，为什么需要主外键关系？

Why is a primary-foreign key relation required when we can join without it?

提问by kawade

If we can get data from two tables without having primary and foreign key relation, then why we need this rule? Can you please explain me clearly, with suitable example? It's a test database, don't mind the bad structure.

如果我们可以在没有主外键关系的情况下从两个表中获取数据，那我们为什么需要这个规则？你能用合适的例子清楚地解释我吗？这是一个测试数据库，不要介意糟糕的结构。

Tables' structure:

表的结构：

**

**

table - 'test1'
columns - id,lname,fname,dob
no primary and foreign key and also not unique(without any constraints)

**

**

**table - 'test2'
columns- id,native_city
again, no relations and no constraints** 

I can still join these tables with same columns 'id', so if there's no primary-foreign key, then what is the use of that?

我仍然可以用相同的列“id”连接这些表，所以如果没有主外键，那有什么用呢？

回答by Daniel Renshaw

The main reason for primary and foreign keys is to enforce data consistency.

主键和外键的主要原因是为了强制数据一致性。

A primary key enforces the consistency of uniqueness of values over one or more columns. If an ID column has a primary key then it is impossible to have two rows with the same ID value. Without that primary key, many rows could have the same ID value and you wouldn't be able to distinguish between them based on the ID value alone.

主键强制一列或多列值的唯一性一致性。如果 ID 列具有主键，则不可能有两行具有相同 ID 值。如果没有该主键，许多行可能具有相同的 ID 值，您将无法仅根据 ID 值区分它们。

A foreign key enforces the consistency of data that points elsewhere. It ensures that the data which is pointed to actually exists. In a typical parent-child relationship, a foreign key ensures that every child always points at a parent and that the parent actually exists. Without the foreign key you could have "orphaned" children that point at a parent that doesn't exist.

外键强制执行指向别处的数据的一致性。它确保所指向的数据确实存在。在典型的父子关系中，外键确保每个子项始终指向父项并且父项确实存在。如果没有外键，您可能会拥有指向不存在的父级的“孤立”子级。

回答by duffymo

You need two columns of the same type, one on each table, to JOIN on. Whether they're primary and foreign keys or not doesn't matter.

您需要两列相同类型的列，每个表一个，以加入。它们是否是主键和外键并不重要。

回答by Jens Schauder

You don't need a FK, you can join arbitrary columns.

您不需要 FK，您可以加入任意列。

But having a foreign key ensures that the join will actually succeed in finding something.

但是拥有外键可以确保连接实际上会成功找到某些东西。

Foreign key give you certain guarantees that would be extremely difficult and error prone to implement otherwise.

外键为您提供了某些保证，否则实施起来将非常困难且容易出错。

For example, if you don't have a foreign key, you might insert a detail record in the system and just after you checked that the matching master record is present somebody else deletes it. So in order to prevent this you need to lock the master table, when ever you modify the detail table (and vice versa). If you don't need/want that guarantee, screw the FKs.

例如，如果您没有外键，您可能会在系统中插入一条详细记录，在您检查匹配的主记录是否存在之后，其他人将其删除。因此，为了防止这种情况，您需要在修改详细表时锁定主表（反之亦然）。如果您不需要/不想要这种保证，请拧紧 FK。

Depending on your RDBMS a foreign key also might improve performance of select (but also degrades performance of updates, inserts and deletes)

根据您的 RDBMS，外键也可能会提高 select 的性能（但也会降低更新、插入和删除的性能）

回答by Francis Rodgers

I know its late to post, but I use the site for my own reference and so I wanted to put an answer here for myself to reference in the future too. I hope you (and others) find it helpful.

我知道发布晚了，但我使用该网站作为我自己的参考，所以我想在这里放一个答案供我自己将来参考。我希望你（和其他人）觉得它有帮助。

Lets pretend a bunch of super Einstein experts designed our database. Our super perfect database has 3 tables, and the following relationships defined between them:

让我们假设一群超级爱因斯坦专家设计了我们的数据库。我们超级完美的数据库有3张表，它们之间定义了如下关系：

TblA 1:M TblB
TblB 1:M TblC

Notice there is no relationship between TblA and TblC

In most scenarios such a simple database is easy to navigate but in commercial databases it is usually impossible to be able to tell at the design stage all the possible uses and combination of uses for data, tables, and even whole databases, especially as systems get built upon and other systems get integrated or switched around or out. This simple fact has spawned a whole industry built on top of databases called Business Intelligence. But I digress...

在大多数情况下，这样一个简单的数据库很容易导航，但在商业数据库中，通常不可能在设计阶段就知道数据、表甚至整个数据库的所有可能用途和用途组合，尤其是当系统变得建立在和其他系统被集成或切换或退出。这个简单的事实催生了一个建立在称为商业智能的数据库之上的整个行业。但我离题了...

In the above case, the structure is so simple to understand that its easy to see you can join from TblA, through to B, and through to C and vice versa to get at what you need. It also very vaguely highlights some of the problems with doing it. Now expand this simple chain to 10 or 20 or 50 relationships long. Now all of a sudden you start to envision a need for exactly your scenario. In simple terms, a join from A to C or vice versa or A to F or B to Z or whatever as our system grows.

在上面的例子中，结构非常容易理解，很容易看出你可以从 TblA 连接到 B，再到 C，反之亦然，以得到你需要的东西。它还非常模糊地突出了这样做的一些问题。现在将这个简单的链扩展到 10、20 或 50 条关系。现在突然之间，您开始设想完全符合您的场景的需求。简单来说，从 A 到 C 或从 A 到 C 或从 A 到 F 或 B 到 Z 或随着我们系统增长的任何连接。

There are many ways this can indeed be done. The one mentioned above being the most popular, that is driving through all the links. The major problem is that its very slow. And gets progressively slower the more tables you add to the chain, the more those tables grow, and the further you want to go through it.

确实有很多方法可以做到这一点。上面提到的那个是最受欢迎的，即驱动所有链接。主要问题是它非常慢。您添加到链中的表越多，这些表增长得越多，并且您想要遍历它的次数越多，速度会逐渐变慢。

Solution 1: Look for a common link. It must be there if you taught of a reason to join A to C. If it is not obvious, create a relationship and then join on it. i.e. To join A through B through C there must be some commonality or your join would either produce zero results or a massive number or results (Cartesian product). If you know this commonality, simply add the needed columns to A and C and link them directly.

解决方案 1：查找公共链接。如果您教导了将 A 加入到 C 的原因，那么它必须存在。如果不明显，请建立关系，然后加入。即要连接 A 到 B 到 C 必须有一些共性，否则您的连接要么产生零结果，要么产生大量结果（笛卡尔积）。如果您知道这种共性，只需将所需的列添加到 A 和 C 并直接链接它们。

The rule for relationships is that they simply must have a reason to exist. Nothing more. If you can find a good reason to link from A to C then do it. But you must ensure your reason is not redundant (i.e. its already handled in some other way).

关系的规则是它们必须有存在的理由。而已。如果你能找到从 A 链接到 C 的充分理由，那就去做吧。但是你必须确保你的理由不是多余的（即它已经以其他方式处理过）。

Now a word of warning. There are some pitfalls. But I don't do a good job of explaining them so I will refer you to my sourceinstead of talking about it here. But remember, this is getting into some heavy stuff, so this video about fan and chasm traps is really only a starting point. You can join without relationships. But I advise watching this video first as this goes beyond what most people learn in college and well into the territory of the BI and SAP guys. These guys, while they can program, their day job is to specialise in exactly this kind of thing. How to get massive amounts of data to talk to each other and make sense.

现在警告。有一些陷阱。但是我没有很好地解释它们，所以我会向你推荐我的消息来源，而不是在这里谈论它。但请记住，这涉及到一些重磅内容，因此这个关于扇形和深渊陷阱的视频实际上只是一个起点。您可以在没有关系的情况下加入。但我建议先观看此视频，因为这超出了大多数人在大学中学到的内容，并且很好地进入了 BI 和 SAP 人员的领域。这些人虽然可以编程，但他们的日常工作就是专门从事这类事情。如何让大量数据相互交流并有意义。

This video is one of the better videos I have come across on the subject. And it's worth looking over some of his other videos. I learned a lot from him.

这个视频是我在这个主题上遇到的更好的视频之一。值得一看的是他的其他一些视频。我从他身上学到了很多。

回答by Adiii

A primary key is not required. A foreign key is not required either. You can construct a query joining two tables on any column you wish as long as the datatypes either match or are converted to match. No relationship needs to explicitly exist.

不需要主键。也不需要外键。只要数据类型匹配或转换为匹配，您就可以在您希望的任何列上构建连接两个表的查询。不需要明确存在关系。

To do this you use an outer join:

为此，您可以使用外部联接：

select tablea.code, tablea.name, tableb.location from tablea left outer join 
tableb on tablea.code = tableb.code

join with out relation

无关系加入

SQL join

SQL 连接