如何跨多个列实现双向唯一索引 [英] How to implement a bidirectional unique index across multiple columns

问题描述

好吧，我知道我可以在应用程序层中做到这一点，这可能是最简单的事情，但是为了确保没有错误渗入数据库，我有一个严重的问题

Okay i know i can do this in the application layer, which is probably the easiest thing to do, but just to make sure that no errors water down to the DB, i have a serious question

我有两个X和Y列，每个列存储两个整数(任何列中的A或B).是否有可能具有唯一的索引约束，因此在任何情况下我们都不应该拥有

I have two columns X and Y, each to store two integers (either A or B in any of the columns). Is it possible to have a unique index constraint such that, in no instance should we have

1. 带有A的X列和带有B的Y列
2. 带有B的X列和带有A的Y列

我给一个场景

我有两个用户，用户A的ID为678498，用户B的ID为679879.这两个用户都将要玩2人游戏，这需要为此会话存储新表(tbl_chalenger).为此，我有一个带有主机"和挑战者"列的表.

I have two users, userA has id 678498 and userB has id 679879. Both users are about to play a 2 player game which requires that a new record for this session be stored in a table (tbl_chalenger). To do so, i have a table with columns "host" and "challenger".

我在tbl_challenger中添加了一个独特的约束

I have a unique constrain added to tbl_challenger as

UNIQUE KEY `UNIQUE_PARTICIPANTS` (`host`,`challenger`)

将用户归为主持人或挑战者的品牌基本上取决于发起游戏的人.因此，如果userA启动游戏，我们将进行如下查询

Branding users a host or challenger is basically dependent on who initiated the game. So if userA initiates the game we have an query as follows

INSERT INTO `tbl_challenger` VALUES(678498 , 679879); 

这将创建一个新记录，但是遗憾的是，如果userB同时尝试与用户A发起游戏，我们会得到

which creates a new record, Sadly though, if at the same time userB attempts to initiate a game with user A, we get

INSERT INTO `tbl_challenger` VALUES(679879, 678498 ); 

这将创建一个新的不需要的行，由相同的参与者组成.这与UNIQUE键约束无关.

Which creates a new unwanted row, of the same participants. This irrespective of the UNIQUE key constraint.

所以我的问题是如何具有双向约束?例如，主机挑战者"和挑战者主机"不能具有相同的数据对

So my question is how to have a constraint that is bidirectional?, such that "host-challenger" as well as "challenger-host" cannot have the same data pair

推荐答案

在mysql中，我唯一想到的方法是添加几个实用程序列，例如

In mysql the only way I can think of is to add a couple of utility columns like

CREATE TABLE tbl_challenger (
  host int,
  challenger int,
  u0 int, u1 int
);

并添加几个将u0和u1设置为两者中最小和最大的触发器:

and add a couple of triggers that set u0 and u1 to the least and greatest of the two:

CREATE TRIGGER uinsert BEFORE INSERT ON tbl_challenger
 FOR EACH ROW SET NEW.u0 = LEAST(NEW.host,NEW.challenger),
  NEW.u1 = GREATEST(NEW.host,NEW.challenger);
CREATE TRIGGER uupdate BEFORE UPDATE ON tbl_challenger
 FOR EACH ROW SET NEW.u0 = LEAST(NEW.host,NEW.challenger),
  NEW.u1 = GREATEST(NEW.host,NEW.challenger);

然后在(u0,u1)

CREATE UNIQUE INDEX uniqueness ON tbl_challenger(u0,u1);

现在，无论顺序如何，尝试插入重复的配对都会出现错误.

And now you will get an error trying to insert duplicate pair regardless of the order.

在像PostgreSQL这样的体面的RDBMS上，您将能够在表达式上使用索引:

On a decent RDBMS like PostgreSQL you would be able to use index on expression:

CREATE UNIQUE INDEX uniqueness ON tbl_challenger
    ( LEAST(host,challenger), GREATEST( host,challenger) );

所以，在为时已晚之前切换；-)

So, switch before it's too late ;-)

这篇关于如何跨多个列实现双向唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！