如何在两个字段上创建“双面"唯一索引? [英] How To Create A 'Two-Sided' Unique Index On Two Fields?

问题描述

如何有效地为表中的两个字段创建唯一索引，如下所示:create table t(a integer, b integer);

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.

换句话说，如果一行存在使得 a=1 和 b=2，则在 a=2 和 b=1 或 a=1 和 b=2 的情况下不能存在另一行.换句话说，两个数字不能以任何顺序同时出现超过一次.

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.

更新:如果我在 (a,b) 列上有一个复合键，并且数据库中存在一行 (1,2)，则可以插入另一行 (2,1) 没有错误.我正在寻找一种方法来防止同一对数字被多次使用以任何顺序...

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 )

这可以通过触发器轻松完成(如 Ronald 的响应)或在应用程序中的更高层处理.

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

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