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.
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 ;