“重复领域”的最佳方式是什么？在SQL中？ [英] What's the best way to have a "repeating field" in SQL?

问题描述

我正在尝试设置一个从不同的表链接两条记录的表。这些链接本身需要与另一个表相关联。所以目前，我的表格如下所示：

I'm trying to set up a table that links two records from a different table. These links themselves need to be correlated to another table. So at the moment, my table looks like this:

link_id    (primary key)
item_id_1  (foreign key)
item_id_2  (foreign key)
link_type  (metadata)

然而，项目之间的链接不是定向的（即它应该没有区别，无论项目是链接中列出的第一个还是第二个）。理想情况下，我想让item_id字段出现两次;因为这是我必须小心，总是检查重复的，以确保从来没有创建连接12到14的记录，如果14到12已经存在。

However, the links between items are not directional (i.e. it should make no difference whether an item is the first or second listed in a link). Ideally, I'd like for the item_id field to just appear twice; as it is I'll have to be careful to always be checking for duplicates to make sure that there's never a record created linking 12 to 14 if 14 to 12 already exists.

有没有一个优雅的数据库设计解决方案，或者我应该只是采用一个约定（例如id_1总是较小的id号码）和警察在应用程序中的重复？

Is there an elegant database design solution to this, or should I just adopt a convention (e.g. id_1 is always the smaller id number) and police duplication within the application?

感谢提前！

推荐答案

Benzado已经指出了 - 添加强制该item_id_1< item_id2：

Benzado already pointed it out - add a constraint that enforces that item_id_1 < item_id2:

ALTER TABLE t ADD CONSTRAINT CH_ITEM1_LESSTHAN_ITEM2 CHECK (item_id_1 < item_id_2)

所以这样可以防止输入错误的数据，拒绝这样的更新/插入。

So this will prevent the wrong data from being entered, rejecting such updates/inserts.

如果您想自动更正item_id_1> item_id_2的任何情况，您可以添加触发器（技术上您可以同时拥有这两个，但是您可能会有一些麻烦获得它可以正常工作，因为在触发器触发之前可以检查检查约束）。触发器的精确语法取决于您的RDBMS。

If you want to automatically correct it any situation where item_id_1 > item_id_2, you could add a trigger instead (technically you could have both, but then you might have some hassle getting it to work right, as check constraints could be checked before the trigger fires). Exact syntax for triggers depends on you RDBMS.

这篇关于“重复领域”的最佳方式是什么？在SQL中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！