How to create an n-n relation on the same table with composite primary key in MySQL

Question

I want to store if two events are combinables. It's a n to n relation, so I need to have a pivot table with these constraints :

• PK(event_1, event_2) = PK(event_2, event_1)
• event_1 is a reference to id in table events
• event_2 is a reference to id in table events

I tried to create my table with this query :

   event_id_1 INT UNSIGNED,
   event_id_2 INT UNSIGNED,
   cumulative_place INT,
   PRIMARY KEY(event_id_1,event_id_2),
   FOREIGN KEY(event_id_1) 
       REFERENCES events(id)
       ON DELETE CASCADE,
   FOREIGN KEY(event_id_2) 
       REFERENCES events(id)
       ON DELETE CASCADE
);

Following MySQL Documentation

I have some questions about that :

1. Is it optimized for search query, for example if i want all events that are combinables with event1 whatever if event1 is in the first column or second ?
2. Do I need to manually manage primary key for INSERT and UPDATE or MySQL do the job ? for example can I just insert cumulative place number for (event1, event2) primary key and if row is store as (event2, event1) this works as well.

question from:https://stackoverflow.com/questions/65899168/how-to-create-an-n-n-relation-on-the-same-table-with-composite-primary-key-in-my

Answer 1

Sort the pair.

INSERT ...
    VALUES (
        LEAST("e1", "e2"),
        GREATEST("e1", "e2"),
        ...)

Similarly, sort the pair when doing a lookup. That way you have 1 row, not 2, and you don't have to look for both orders.