mysql多对多关系 [英] mysql many to many relationship

问题描述

阅读本教程如何处理多对多与PHP和MySQL的关系.

在这个问题中，我指的是数据库架构"部分，其中规定了以下规则:

In this question I refer to the "Database schema" section which states the following rules:

此新表必须构造为 允许以下内容:

This new table must be constructed to allow the following:

* It must have a column which links back to table 'A'.
* It must have a column which links back to table 'B'.
* It must allow no more than one row to exist for any combination of rows from table 'A' and table 'B'.
* It must have a primary key.

到目前为止，一切都很清晰.

Now it's crystal clear so far.

我唯一遇到的问题是第三条规则(对于任何组合，它必须允许不超过一行的行".)
我也希望应用此方法，但似乎无法以这种方式工作.

The only problem I'm having is with the 3rd rule ("It must allow no more than one row to exist for any combination").
I want this to be applied as well, but it doesn't seem to work this way.

在mysql(5.XX)的测试实例上，我可以添加两行来反映相同的关系！

On my test instance of mysql (5.XX) I'm able to add two rows which reflect the same relationship!

例如，如果我建立此关系(通过添加一行):
A到B
它还使我也可以建立这种关系:
B到A

For example, if I make this relation (by adding a row):
A to B
It also allows me to make this relation as well:
B to A

所以问题实际上是两个问题:

So the question is two questions actually:

1) 我该如何禁止执行上述第三项规则? ?无论组合如何，只有一个唯一的关系.

1) How do I enfore the 3rd rule which will not allow to do the above? Have only one unique relation regardless of the combination.

2)当我想 搜索"A"的所有关系时，SQL查询的外观如何 ?

2) When I'll want to search for all the relations of 'A', how would the SQL query look like?

注#1:基本上，我的最终目标是创建一个友谊"系统，据我所知，解决方案是一个多对多表.如果可能的话，建议其他方式.

Note #1: Basically my final goal is to create a "friendship" system, and as far as I understand the solution is a many-to-many table. Suggest otherwise if possible.

注释#2: users表与该关系位于不同的数据库中(称为友谊表)表.因此，我不能使用外键.

Note #2: The users table is on a different database from the relations (call it friendships) table. Therefore I cannot use foreign keys.

推荐答案

第一个问题:

1. 在两者上创建唯一约束 列
2. 确保始终对列进行排序.因此，如果您的桌子上有 列a和b比确保 a小于或等于 b

1. Create a unique constraint on both columns
2. Make sure you always sort the columns. So if your table has the colummns a and b than make sure that a is less than or equal to b

第二个问题:

SELECT
  *
FROM
  many_to_many_table
WHERE
  a = A or b = A

这篇关于mysql多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！