Mysql – Insert Row With Foreign Key Equal to ID – Circular Reference

foreign keyMySQL

In MySQL database I have an events table which has a circular reference to itself with the following columns.

id (PK) | user_id (FK) | event_id (FK) | title

Each new row inserted has a circular reference to another row in the table. The problem is how do I create the first event for each user?

I have tried manually inserting the IDs so i know it is possible to have an event reference itself. For example if i send an explicit query with id = 1, user_id = 1, event_id = 1, title = test then the row is added fine and then adding other events for that user is then straightforward.

The problem is that this solution is only appropriate for testing. In production, when a user wants to create their first event, how can I construct a query so it sets the event_id to the same value as the new auto increment value on the id column?

To clarify, the first event for a user will throw a foriegn key constraint error as I do not know the event_id that should be set.

As requested

CREATE TABLE `events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  PRIMARY KEY 
    (`id`),
  UNIQUE KEY `events_event_id_title_unique` 
    (`event_id`,`title`),
  KEY `events_user_id_foreign` 
    (`user_id`),
  CONSTRAINT `events_event_id_foreign`
    FOREIGN KEY (`event_id`) 
    REFERENCES `events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `events_user_id_foreign` 
    FOREIGN KEY (`user_id`) 
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB 
  AUTO_INCREMENT=14 
  DEFAULT CHARSET=utf8 ;

Best Answer

Solution 1:

Make the event_id nullable.

ALTER TABLE events
  MODIFY COLUMN event_id int unsigned NULL ;

This will allow you to insert rows which reference nothing (NULL): 

INSERT INTO events
  (user_id, event_id, title)
VALUES
  (a_valid_user_id, NULL, 'test event 1') ;

And to see events without a "parent" event:

SELECT *
FROM events
WHERE event_id IS NULL ;

Test at SQL-Fiddle-1

(Parenthesis)

Unless you have some braindead ORM that requires all tables to have a column named id, it's better to have names that describe what they represent. I'd rename id to event_id and event_id to parent_event_id:

CREATE TABLE events (
  event_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(10) unsigned NOT NULL,
  parent_event_id int(10) unsigned NULL,
  title varchar(200) NOT NULL,
  PRIMARY KEY (event_id),
  --- ...
) ;
Related Question