What's the best way to have a "repeating field" in SQL?

Question

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)

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.

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?

Thanks in advance!

Answer 1

You could use a join table.

Table1: link_id (PK), link_type
JoinTable: table1_link_id, item_id (composite primary key composed of both ids)

Answer 2

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.

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.

Answer 3

There's a couple of ways to implement this. One would be with a combination of a check constraint and a unique constraint.

alter table t23 
   add constraint c1_c2_ck check (c1 < c2)
/
alter table t23 
   add constraint t23_uk unique (c1, c2)
/

That would work in most DBMS flavours. An alternative approach, which would work in Oracle at least, would be to use a function-based index ....

create unique index t23_uidx on t23
    (least(c1,c2), greatest(c1,c2))
/