Linking 2 ID's in MySQL

Question

I have this table

CREATE TABLE IF NOT EXISTS `links` (
  `link_id` int(20) NOT NULL AUTO_INCREMENT,
  `item1_id` int(20) NOT NULL,
  `item2_id` int(20) NOT NULL,
  PRIMARY KEY (`link_id`),
  UNIQUE KEY `item_id` (`item1_id`,`item2_id`)
) ENGINE=InnoDB;

How can I constrain it so that the item_id can only appear once in item1_id OR item2_id ?

Since I want a item to be only linked to one other item..

Answer 1

I want a item to be only linked to one other item

This means you don't need a link table. You simply need a linkedItemId column in your Item table with a unique constraint on it. As soon as Item2 is linked to Item1 (Item1ID is in linkedItemId for Item2 row), then nothing else can link to Item1.

Also, link table do not need their own surrogate keys

Edit, note that MySQL allows multiple NULLs in a unique index (unlike, say, SQL Server, where you'd use a filtered unique index to ignore NULLs)

From MySQL 5.5 CREATE INDEX

For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL

Answer 2

I don't think INNODB can enforce such a constraint (or MyIsam for that matter). My suggestion would be to create a stored procedure to handle the insert. First check your custom constraint needs, then insert as usual if no conflict.

Answer 3

You could shrink the UNIQUE KEY to have only item1_id. This means that the table defines a 1:1 relationship and not a 1:n one. Additionally, you can remove the auto_increment primary key, it's not needed in these "link" tables:

CREATE TABLE IF NOT EXISTS links (
   item1_id int(20) NOT NULL,
   item2_id int(20) NOT NULL,
  PRIMARY KEY (item1_id),
  FOREIGN KEY (item1_id)                 --- I assume you have these 2
    REFERENCES item (item_id),           --- Foreign Keys, too
  FOREIGN KEY (item2_id)
    REFERENCES item (item_id)
) ENGINE=InnoDB;

The difference between this and @gbn's answer is that this does not allow Nulls, and does not need any to store an item that is not linked. Both designs work almost the same, with minor modifications in the Insert/Delete/Update statements.

In both designs though, we can have linked couples like: (1 -> 2), (2 -> 3), (3 -> 7). If that meets the wanted specification, both designs are fine.

---

If however, we only want items to appear in only one linked couple, in either side of the link, it's harder to implement.

One way would be to ensure that all Inserts in the links table are done via a procedure that either inserts both (1, 2) and (2, 1) couples or fail (and similarly for Delete/Update statements that will have to deal with 2 rows).

Other more complicated ways involve triggers (or exotic structures, like indexes views, not available in MySQL).

If you want a normalized design, there is also this approach (complicated but without triggers):

CREATE TABLE IF NOT EXISTS link_help (
   item1_id int(20) NOT NULL,
   item2_id int(20) NOT NULL,
  PRIMARY KEY (item1_id),
  FOREIGN KEY (item1_id) 
    REFERENCES item (item_id),  
  FOREIGN KEY (item2_id)
    REFERENCES item (item_id),
  UNIQUE KEY (item1_id, item2_id)          --- this will be needed below
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS links (
   item1_id int(20) NOT NULL,
   item2_id int(20) NOT NULL,
  PRIMARY KEY (item1_id),
  FOREIGN KEY (item1_id, item2_id) 
    REFERENCES link_help (item1_id, item2_id), 
  FOREIGN KEY (item2_id, item1_id)               --- notice the different 
    REFERENCES link_help (item1_id, item2_id)    --- order here
) ENGINE=InnoDB;

Now, you cannot add (1 -> 2), (2 -> 3), (3 -> 7) rows in the links table.

Answer 4

...and there's also the question of whether the links have an explicit direction - i.e. is A-B different from B-A?

I think (you'll need to test this) that you can deal with both cases using a trigger. However I'm not aware of any method by which you can explicitly throw an error from with MySQL's procedural language. Since you've specified that the columns should be NOT NULL with no default, and assuming that NEW is writable, then....

CREATE TRIGGER ins_link BEFORE INSERT on links
FOR EACH ROW
BEGIN
  IF (NEW.item1_id = NEW.item2_id) THEN
      NEW.item2_id=NULL; /* subsequent INSERT will fail */
  END IF
  /* if you want AB=BA.... */
  IF (NEW.item1_id > NEW.item2_id) THEN
     @tempvar=NEW.item1_id;
     NEW.item1_id=NEW.item2_id;
     NEW.item2_id=@tempvar;
  END IF;
END;

(you may need a before update trigger too but remainder of code is same).