Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ;
share|improve this question
1  
Add the CREATE TABLE statement. –  ypercube May 17 '13 at 21:20
1  
I mean the output of SHOW CREATE TABLE events ; –  ypercube May 17 '13 at 21:28
2  
SHOW CREATE TABLE events\G will display the SQL statement that will create the table as well as the storage engine the table is using. This is what @ypercube was asking you. When you run this command, copy and paste the output of SHOW CREATE TABLE events\G into the question. –  RolandoMySQLDBA May 17 '13 at 21:34
    
And do not cross-post: stackoverflow.com/questions/16618231/… –  ypercube May 17 '13 at 21:41
    
OK, I assume that the events_event_id_foreign designates a "parent" event relationship? –  ypercube May 17 '13 at 21:48

1 Answer 1

up vote 3 down vote accepted

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),
  --- ...
) ;
share|improve this answer
    
perfect, thanks. –  Gaz_Edge May 17 '13 at 22:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.