如何在两个字段上创建“双面”唯一索引？

问题描述

如何在表中的两个字段上有效地创建唯一索引，如下所示：
create table t（a integer，b integer）;

其中两个不同数字的任何唯一组合不能在表中的同一行上出现多于一次。

如果存在行，a = 1和b = 2，另一行不能存在，其中a = 2和b = 1或a = 1和b = 2。换句话说，两个数字不能以任何顺序一起出现在一起。

我不知道这样的约束被调用，因此双边唯一索引

：如果我在列（a，b）和行（1,2）上有复合键）存在于数据库中，可以插入另一行（2,1）而没有错误。我正在寻找的是防止同一对数字被多次使用 以任何顺序 ...

解决方案

如何控制进入表中的内容，以便始终将最小的数字存储到第一列，最大的存储在第二列中？只要它'意味着'同样的事情当然。

如果这是不可能的，你可以保存字段，但它们按数字顺序复制到数据库中。两个其他字段，您将在其中创建主键（伪代码）：

  COLUMN A：2 
 COLUMN B：1 
 
 COLUMN A_PK：1（如果new.a  COLUMN B_PK：2（如果new.b> ; new.a then new.b else new.a）
  

这可以很容易地通过触发器（如在Ronald的回答中）或在应用程序中处理得更高。

How can I efficiently create a unique index on two fields in a table like this:create table t (a integer, b integer);

where any unique combination of two different numbers cannot appear more than once on the same row in the table.

In order words if a row exists such that a=1 and b=2, another row cannot exist where a=2 and b=1 or a=1 and b=2. In other words two numbers cannot appear together more than once in any order.

I have no idea what such a constraint is called, hence the 'two-sided unique index' name in the title.

Update: If I have a composite key on columns (a,b), and a row (1,2) exists in the database, it is possible to insert another row (2,1) without an error. What I'm looking for is a way to prevent the same pair of numbers from being used more than once in any order...

解决方案

How about controlling what goes into the table so that you always store the smallest number into the first column and the largest one in the second? As long as it 'means' the same thing of course. It's probably less expensive to do it before it even gets to the database.

If this is impossible, you could save the fields as is but have them duplicated in numerical order into two OTHER fields, on which you would create the primary key (pseudo code-ish) :

COLUMN A : 2
COLUMN B : 1

COLUMN A_PK : 1  ( if new.a < new.b then new.a else new.b )
COLUMN B_PK : 2  ( if new.b > new.a then new.b else new.a )

This could easily be done with a trigger (as in Ronald's response) or handled higher up, in the application.

这篇关于如何在两个字段上创建“双面”唯一索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！