blocks|key|247473|text|如果您的RDBMS+(您没有指定)支持触发器，那么在该表上创建一个触发器来执行您的约束。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|247474|创建一个触发器，在INSERT上触发，检查一对是否已经存在，顺序是否颠倒。如果确实回滚，则允许插入。|247475|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

If your RDBMS (you didn't specify) supports Triggers, then create a trigger on that table to enforce your constraint.

Create a trigger that fires on INSERT, that checks if a pair already exists with order reversed. If it does ROLLBACK, else allow the insert.

blocks|key|99430|text|定义一个约束，例如，A列中的值必须小于B列中的值:因此允许插入{+blue，red}，但不插入{+red，blue}，因为蓝色小于红色。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|99431|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Define a constraint such that, for example, the value in the A column must be (alphabetically or numerically) smaller than the value in the B column: thus you'd be allowed to insert {blue,red} but not {red,blue} because blue is less than red.

blocks|key|277828|text|下面是一些用于Mitch描述的触发器方法的示例代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|277829|我还没有测试过这个代码，现在已经是深夜了:-)|277830|CREATE+TRIGGER+trig_addTeam
ON+Teams
FOR+INSERT,+UPDATE
AS

DECLARE+@TeamA+VARCHAR(100)+
DECLARE+@TeamB+VARCHAR(100)
DECLARE+@Count+INT

SELECT+@TeamA+=+(SELECT+TeamA+FROM+Inserted)
SELECT+@TeamB+=+(SELECT+TeamB+FROM+Inserted)

SELECT+@Count+=+(SELECT+COUNT(*)+FROM+TEAMS+WHERE+(TeamA+=+@TeamA+AND+TeamB+=+@TeamB)
+++++++++++++++++OR+(TeamA+=+@TeamB+AND+TeamB+=+@TeamA))

IF+@Count+>+0+THEN

BEGIN
++++ROLLBACK+TRANSACTION
END+|code-block|syntax|javascript|277831|这样做的目的是查看当前表中是否存在A、x、B或B%2B%2B的序列。如果是，则返回的计数大于零，事务将回滚，而不提交给数据库。|277832|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here is some sample code for use with the trigger method that Mitch described.

I have not tested this code, and it's late at night here :-)

<pre><code>CREATE TRIGGER trig_addTeam
ON Teams
FOR INSERT, UPDATE
AS

DECLARE @TeamA VARCHAR(100) 
DECLARE @TeamB VARCHAR(100)
DECLARE @Count INT

SELECT @TeamA = (SELECT TeamA FROM Inserted)
SELECT @TeamB = (SELECT TeamB FROM Inserted)

SELECT @Count = (SELECT COUNT(*) FROM TEAMS WHERE (TeamA = @TeamA AND TeamB = @TeamB)
 OR (TeamA = @TeamB AND TeamB = @TeamA))

IF @Count &gt; 0 THEN

BEGIN
 ROLLBACK TRANSACTION
END 
</code></pre>

What this is doing is looking to see if either sequence of A|B or B|A exists in the current table. If it does then the count returned is greater than zero, and the transaction is rolled back and not committed to the database.

blocks|key|278030|text|如果存在相同的对(反向)，则取其中一个TeamA>TeamB。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|278031|SELECT+DISTINCT+TeamA,+TeamB+
FROM+table+t1+
WHERE+t1.TeamA+>+t1.TeamB+
++++OR+NOT+EXISTS+(
++++++++SELECT+*+FROM+table+t2+
++++++++++++WHERE+t2.TeamA+=+t1.TeamB+AND+t2.TeamB+=+t1.TeamA+
++++)|code-block|syntax|javascript|278032|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If the same pair (reversed) exists take the one where TeamA>TeamB.

<pre><code>SELECT DISTINCT TeamA, TeamB 
FROM table t1 
WHERE t1.TeamA &gt; t1.TeamB 
 OR NOT EXISTS (
 SELECT * FROM table t2 
 WHERE t2.TeamA = t1.TeamB AND t2.TeamB = t1.TeamA 
 )
</code></pre>

If I have a joining table with two columns, TeamA and TeamB, how can I ensure that each pair is unique?

Obviously I can put a unique composite index on these columns but that will only ensure uniqueness in the order A,B but not B,A correct?

<pre><code>TeamA | TeamB
-------------
 red | blue
 pink | blue
 blue | red
</code></pre>

As you can see, Red vs. Blue has already been specified as the first record and then it is specified again as the last. This should be illegal since they will already be facing each other.

Edit: Also, is there a way to handle the SELECT case as well? Or the UPDATE? DELETE? Etc..

Also, the idea of Home or Away team has been brought up which may be important here. This initial concept came to me while thinking about how to build a bracketing system on the DB side.

Unique column pairs as A,B or B,A

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如果我有一个包含两个列( TeamA和TeamB )的联接表，那么如何确保每一对都是唯一的呢？很明显，我可以在这些列上加一个唯一的综合索引，但这只会确保A，B的唯一性，而不是B，A，对吗？TeamA | TeamB------------- red  | blue pink | blue blue | red如您所见，...

问唯一列对为A，B或B，A
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问唯一列对为A，B或B，AEN