CODEWITHSUNDEEP
mysqlsql
Stéphane Conq
Stéphane Conq

Reputation: 48

Primary key couple unique in both ways

Here is my problem :

I want to create relations between two items of the same type.

Basically, I have a 'tag' table and I want to establish relations between tags.

I created a table 'tag_relations'. In this table, each line would represent one relation. It will be composed with 2 atributes : tag_id (which is the id of the tag concerned by the relation), and relation (which is the id of the tag related to the first tag represented by tag_id).

I set the primary key of my 'tag_relations' table as the couple of these 2 attributes.

Here is how I created my table : 

CREATE TABLE `tag_relation` (
  `tag_id` int(11) NOT NULL,
  `relation` int(11) NOT NULL,
  PRIMARY KEY (`tag_id`,`relation`),
  KEY `relation` (`relation`),
  CONSTRAINT `tag_relation_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`),
  CONSTRAINT `tag_relation_ibfk_2` FOREIGN KEY (`relation`) REFERENCES `tag` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My question is : how can I make sure, if I already have a couple (1,2) in my table (so tag 1 is related to tag 2), that it's impossible for me to insert the couple (2,1) (because this relation would already exist implicitly).

Am I forced to create a trigger ?

Thanks in advance

Upvotes: 1

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

Some databases support indexes on expressions. Since 5.7, MySQL has generated columns with indexes. This allows you to do:

alter table tag_relation add tag1 generated always as (least(tag1, relation));
alter table tag_relation add tag2 generated always as (greatest(tag1, relation));

create unique index unq_tag_relation_tag1_tag2 on tag_relation(tag1, tag2);

In earlier versions, you would need an insert (and possibly update) trigger to ensure data integrity.

Upvotes: 1

Related Questions