問題描述

我有一個數據結構，我必須在其中存儲元素對.每對中正好有 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.

這篇關于對兩個字段的唯一約束，以及它們的相反的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！