对两个字段的唯一约束，以及它们的相反

2021-09-14 00:00:00 sql unique sql-server-2008 sql-server

我有一个数据结构，我必须在其中存储元素对.每对中正好有 2 个值，因此我们使用了一个表，其中包含字段 (leftvalue, rightvalue....).这些对应该是唯一的，如果键被更改，它们被认为是相同的.

I have a data structure, where I have to store pairs of elements. Each pair has exactly 2 values in it, so we are employing a table, with the fields(leftvalue, rightvalue....). These pairs should be unique, and they are considered the same, if the keys are changed.

Example: (Fruit, Apple) is the same as (Apple, Fruit).

如果可能以一种有效的方式，我会在字段上设置数据库约束，但不会以任何代价 - 性能更重要.

If it is possible in an efficient way, I would put a database constraint on the fields, but not at any cost - performance is more important.

我们目前使用的是 MSSQL server 2008，但可以更新.

We are using MSSQL server 2008 currently, but an update is possible.

有没有有效的方法来实现这一目标?

Is there an efficient way of achieving this?

推荐答案

两种解决方案，实际上都是将问题变得更简单.如果可以接受强制改变消费者，我通常更喜欢 T1 解决方案:

Two solutions, both really about changing the problem into an easier one. I'd usually prefer the T1 solution if forcing a change on consumers is acceptable:

create table dbo.T1 (
    Lft int not null,
    Rgt int not null,
    constraint CK_T1 CHECK (Lft < Rgt),
    constraint UQ_T1 UNIQUE (Lft,Rgt)
)
go
create table dbo.T2 (
    Lft int not null,
    Rgt int not null
)
go
create view dbo.T2_DRI
with schemabinding
as
    select
        CASE WHEN Lft<Rgt THEN Lft ELSE Rgt END as Lft,
        CASE WHEN Lft<Rgt THEN Rgt ELSE Lft END as Rgt
    from dbo.T2
go
create unique clustered index IX_T2_DRI on dbo.T2_DRI(Lft,Rgt)
go

在这两种情况下，T1 和 T2 都不能在 Lft,Rgt 对中包含重复值.

In both cases, neither T1 nor T2 can contain duplicate values in the Lft,Rgt pairs.

相关文章