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 on 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”连接这些表，所以如果没有主外键，那么它的用途是什么?
5 个解决方案
#1
48
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.
外键加强了指向别处的数据的一致性。它确保指向实际存在的数据。在典型的父子关系中，外键确保每个子键都指向父节点，并且父节点实际上存在。如果没有外键，你就可以在一个不存在的父母那里“孤立”孩子。
#2
19
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.
您需要两个相同类型的列，一个在每个表上，以连接。它们是主键还是外键都不重要。
#3
15
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.
但是，拥有一个外键可以确保join能够成功地找到一些东西。
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.
例如，如果您没有外键，您可以在系统中插入一个详细记录，在您检查匹配的主记录是否存在之后，其他人将删除它。因此，为了防止这一点，您需要锁定主表，当您修改详细表时(反之亦然)。如果你不需要担保，那就去他妈的FKs。
Depending on your RDBMS a foreign key also might improve performance of select (but also degrades performance of updates, inserts and deletes)
根据RDBMS的不同，外键也可能提高select的性能(但也会降低更新、插入和删除的性能)
#4
6
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:
让我们假设一群超级爱因斯坦专家设计了我们的数据库。我们的super perfect database有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到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 source instead 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.
这个视频是我遇到的关于这个主题的更好的视频之一。他的其他视频值得一看。我从他那里学到了很多。
#5
1
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.
不需要主键。也不需要外键。只要数据类型匹配或转换为match，就可以在任何列上构造连接两个表的查询。没有关系需要明确存在。
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
加入了关系
SQL加入
#1
48
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.
外键加强了指向别处的数据的一致性。它确保指向实际存在的数据。在典型的父子关系中，外键确保每个子键都指向父节点，并且父节点实际上存在。如果没有外键，你就可以在一个不存在的父母那里“孤立”孩子。
#2
19
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.
您需要两个相同类型的列，一个在每个表上，以连接。它们是主键还是外键都不重要。
#3
15
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.
但是，拥有一个外键可以确保join能够成功地找到一些东西。
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.
例如，如果您没有外键，您可以在系统中插入一个详细记录，在您检查匹配的主记录是否存在之后，其他人将删除它。因此，为了防止这一点，您需要锁定主表，当您修改详细表时(反之亦然)。如果你不需要担保，那就去他妈的FKs。
Depending on your RDBMS a foreign key also might improve performance of select (but also degrades performance of updates, inserts and deletes)
根据RDBMS的不同，外键也可能提高select的性能(但也会降低更新、插入和删除的性能)
#4
6
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:
让我们假设一群超级爱因斯坦专家设计了我们的数据库。我们的super perfect database有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到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 source instead 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.
这个视频是我遇到的关于这个主题的更好的视频之一。他的其他视频值得一看。我从他那里学到了很多。
#5
1
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.
不需要主键。也不需要外键。只要数据类型匹配或转换为match，就可以在任何列上构造连接两个表的查询。没有关系需要明确存在。
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
加入了关系
SQL加入