避免外键组合的约束 [英] Constraint to avoid combination of foreign keys

问题描述

我这里有一个问题，我无法在研究中找到合适的解决方案，也许是因为我找不到确切的搜索条件，因此，如果重复的话，我将其删除。

I've here a problem that I couldn't find a proper solution on my researches, maybe it's because I couldn't find out the exact terms to search for it, so if this is a duplicate I will delete it.

我的问题是我想知道是否有可能避免两个字段之间的数据组合。我将显示我想要避免的数据的结构和种类。会更容易理解。

My problem is I want to know if it is possible to avoid a combination of data between two fields. I will show the structure and the kind of data I want to avoid. It will be easier to understand.

Table_A                         Table_B
------------------------        -------------------------------               
id integer (PK)                 id integer (PK) 
description varchar(50)         title varchar(50)
                                id1_fromA  (FK A->id)
                                id2_fromA  (FK A->id)

我正在尝试验证表 Table_B 上的以下数据（组合在id1_fromA和id2_fromA之间）

I'm trying to validate the following data on table Table_B (combination is between id1_fromA and id2_fromA)

id   title          id1_fromA  id2_fromA  
1    Some Title         1         2 --It will be permmited
2    Some other         1         2 --It is a duplicate NOT ALLOWED
3    One more           1         1 --It is equals NOT ALLOWED
4    Another            2         1 --It is same as registry id 1 so NOT ALLOWED
5    Sample data        3         2 --It is ok

有了上述数据，我可以轻松地用

$ b $解决注册表 ID = 2 的问题b

With above data I can easily solve the problem for registry ID=2 with

ALTER TABLE table_B ADD CONSTRAINT UK_TO_A_FKS UNIQUE (id1_fromA, id2_fromA);

注册表问题 ID = 3 用

ALTER TABLE table_B ADD CONSTRAINT CHK_TO_A_FKS CHECK (id1_fromA != id2_fromA);

我的问题是注册表 ID = 4 我想避免重复这样的组合，例如 1,2 = 2,1 。是否可以使用 CONSTRAINT 或 INDEX 或 UNIQUE 还是我需要创建一个触发器或过程来这样做？

My Problem is with the registry ID=4 I want to avoid such duplicate of combination as 1,2=2,1. Is it possible to do it with a CONSTRAINT or an INDEX or an UNIQUE or I will need to create a trigger or a procedure to do so?

预先感谢。

推荐答案

您不能使用唯一的约束来执行此操作，但是可以使用唯一的 index 来执行此操作。

You can't do this with a unique constraint, but you can do this with a unique index.

create unique index UK_TO_A_FKS 
   on table_b (least(id1_froma, id2_froma), greatest(id1_froma, id2_froma));

这篇关于避免外键组合的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！