I'm trying to create a constraint on table A to check if a set of records exists in a table B. I could use a foreign key, but the problem is that the data in the table B is non-unique.
我试图在表a上创建一个约束，以检查表B中是否存在一组记录。我可以使用外键，但问题是表B中的数据是非惟一的。
Is there a way to create such a constraint without creating a trigger?
有没有一种方法可以在不创建触发器的情况下创建这样的约束?
Edit: I can't change table B structure.
编辑:我不能改变B表的结构。
3 个解决方案
#1
3
One technique would be to use a materialised view (fast refresh on commit) to store the unique values of the referenced column, and constrain your table against that.
一种技术是使用一个实体化的视图(快速刷新提交)来存储引用列的惟一值，并约束您的表。
Attempts at using triggers to enforce integrity are generally doomed due to read consistency or locking issues.
由于读取一致性或锁定问题，试图使用触发器来加强完整性通常是注定要失败的。
#2
4
Foreign keys are a 1:N relationship. There can only be one parent record at the referenced end of the constraint. That's why we can only build foreign key constraints which reference unique keys.
外键是1:N的关系。在约束的引用端只能有一个父记录。这就是为什么我们只能构建引用唯一键的外键约束。
You appear to be want a constraint which is M:N. This does not fit in a relational model. Perhaps what you need is a intersection table (AB) which links many records in table A with many records in table B? In fact, there may be several different modelling solutions, depending on your actual requirements.
你似乎想要一个约束，它是M:N。这并不适用于关系模型。也许您需要的是一个交集表(AB)，它将表a中的许多记录与表B中的许多记录连接在一起。事实上，根据您的实际需求，可能有几种不同的建模解决方案。
Triggers won't work, partly because they won'tr scale but mainly because they won't work in a multi-user environment.
触发器不能工作，部分原因是它们无法伸缩，但主要是因为它们不能在多用户环境中工作。
#3
0
I'm pretty sure the only way to enforce such a relationship is with a trigger.
我很确定，加强这种关系的唯一方法就是使用触发器。
As you mention, the data in table B is non-unique, so a foreign key will not work. (See also Can a foreign key reference a non-unique index?)
正如您所提到的，表B中的数据是非惟一的，因此外键不能工作。(参见外键是否可以引用非唯一索引?)
Check constraints come to mind, but they will not work here because:
检查约束条件，但在这里不起作用，因为:
- must not reference other tables
- 不能引用其他表吗?
- cannot contain subqueries.
- 不能包含子查询。
That being said, it may be possible that the reason the data in table B is not unique is that it is not normalized. It may be worth reviewing your schema to see if you can extract a unique relationship between A and B, possibly with the use of an intermediate table.
也就是说，表B中的数据并非唯一的原因可能是它没有规范化。检查您的模式是否值得，看看您是否可以在a和B之间提取一种特殊的关系，可能是使用中间表。
#1
3
One technique would be to use a materialised view (fast refresh on commit) to store the unique values of the referenced column, and constrain your table against that.
一种技术是使用一个实体化的视图(快速刷新提交)来存储引用列的惟一值，并约束您的表。
Attempts at using triggers to enforce integrity are generally doomed due to read consistency or locking issues.
由于读取一致性或锁定问题，试图使用触发器来加强完整性通常是注定要失败的。
#2
4
Foreign keys are a 1:N relationship. There can only be one parent record at the referenced end of the constraint. That's why we can only build foreign key constraints which reference unique keys.
外键是1:N的关系。在约束的引用端只能有一个父记录。这就是为什么我们只能构建引用唯一键的外键约束。
You appear to be want a constraint which is M:N. This does not fit in a relational model. Perhaps what you need is a intersection table (AB) which links many records in table A with many records in table B? In fact, there may be several different modelling solutions, depending on your actual requirements.
你似乎想要一个约束，它是M:N。这并不适用于关系模型。也许您需要的是一个交集表(AB)，它将表a中的许多记录与表B中的许多记录连接在一起。事实上，根据您的实际需求，可能有几种不同的建模解决方案。
Triggers won't work, partly because they won'tr scale but mainly because they won't work in a multi-user environment.
触发器不能工作，部分原因是它们无法伸缩，但主要是因为它们不能在多用户环境中工作。
#3
0
I'm pretty sure the only way to enforce such a relationship is with a trigger.
我很确定，加强这种关系的唯一方法就是使用触发器。
As you mention, the data in table B is non-unique, so a foreign key will not work. (See also Can a foreign key reference a non-unique index?)
正如您所提到的，表B中的数据是非惟一的，因此外键不能工作。(参见外键是否可以引用非唯一索引?)
Check constraints come to mind, but they will not work here because:
检查约束条件，但在这里不起作用，因为:
- must not reference other tables
- 不能引用其他表吗?
- cannot contain subqueries.
- 不能包含子查询。
That being said, it may be possible that the reason the data in table B is not unique is that it is not normalized. It may be worth reviewing your schema to see if you can extract a unique relationship between A and B, possibly with the use of an intermediate table.
也就是说，表B中的数据并非唯一的原因可能是它没有规范化。检查您的模式是否值得，看看您是否可以在a和B之间提取一种特殊的关系，可能是使用中间表。