“友谊"中独一无二的一对数据库 [英] unique pair in a "friendship" database

问题描述

我发布的这个问题是我其他问题的总结.

I'm posting this question which is somewhat a summary of my other question.

我有两个数据库:
1) db_users.
2) db_friends.

I have two databases:
1) db_users.
2) db_friends.

我强调它们存储在不同服务器上的独立数据库中，因此不能使用外键.

I stress that they're stored in separate databases on different servers and therefore no foreign keys can be used.

在db_friends"中，我有一个表tbl_friends"，其中包含以下列:
- id_user
- id_friend

In 'db_friends' I have the table 'tbl_friends' which has the following columns:
- id_user
- id_friend

现在我如何确保这张桌子上的每一对都是独一无二的('tbl_friends')?
我想在表级别执行此操作，而不是通过查询.

Now how do I make sure that each pair is unique at this table ('tbl_friends')?
I'd like to enfore that at the table level, and not through a query.

例如这些是无效行:
1 - 2
2 - 1

For example these are invalid rows:
1 - 2
2 - 1

我希望这个是不可能添加的.

I'd like this to be impossible to add.

另外 - 我将如何搜索用户 713 的所有朋友虽然他可以被提及，在一些友谊行，在第二列('id_friend')?

Additionally - how would I seach for all of the friends of user 713 while he could be mentioned, on some friendship rows, at the second column ('id_friend')?

推荐答案

您可能无法在数据库级别执行此操作——您的应用程序代码将不得不执行此操作.如果您确保您的 tbl_friends 记录始终包含 (lowId, highId)，那么典型的 PK/唯一索引将解决重复问题.事实上，我会将你的 tbl_friends 中的列重命名为 (id_low, id_high) 只是为了加强这一点.

You're probably not going to be able to do this at the database level -- your application code is going to have to do this. If you make sure that your tbl_friends records always go in with (lowId, highId), then a typical PK/Unique Index will solve the duplicate problem. In fact, I'd go so far to rename the columns in your tbl_friends to (id_low, id_high) just to reinforce this.

您要查找用户 713 的任何内容的查询将类似于

Your query to find anything with user 713 would then be something like

SELECT id_low AS friend FROM tbl_friends WHERE (id_high = ?)
UNION ALL
SELECT id_high AS friend FROM tbl_friends WHERE (id_low = ?)

为了效率，您可能希望向前和向后索引它 - 即通过 (id_user, id_friend) 和 (id_friend, id_user).

For efficiency, you'd probably want to index it forward and backward -- that is by (id_user, id_friend) and (id_friend, id_user).

如果您必须在数据库级别执行此操作，那么在插入之前将参数交换到(低，高)的存储过程将起作用.

If you must do this at a DB level, then a stored procedure to swap arguments to (low,high) before inserting would work.

这篇关于“友谊"中独一无二的一对数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！