Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Self Referencing Foreign Key Struggle

    Ok, so my problem is inserting the initial "base" record into my classes table. Here is the code along with the received error. It looks ok from what I see, and this error has been killing me. I want to keep the foreign key constraints. Any suggestions would be useful. Thanks.

    Code:
    drop table if exists classes;
    create table classes
    (
      id int unsigned not null auto_increment,
      name varchar(50) not null,
      description tinytext,
      parent_class_id int unsigned not null default 0,
      
      
      index(parent_class_id),
      foreign key(parent_class_id) references classes(id),
    
      primary key(id) 
    )ENGINE=InnoDB;
                           
    /*
    This would bee the "base" class that all other classes inherit from by default.
    The problem is creating this first record with the foreign key value referring to the same primary key value, and both being created at the same time.
    */
    insert into classes 
    values(0,'base','description blah blah....',0);
    
    /*
    These are the following results
    
    Query OK, 0 rows affected (0.34 sec)
    
    Query OK, 0 rows affected (0.19 sec)
    
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`proj`.`classes`, CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`parent_class_id`) REFERENCES `classes` (`id`))
    */

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you cannot insert the first class because it stipulates a parent_id of 0, which doesn't exist

    the top class in the hierarchy must have a NULL parent_id instead
    Code:
    CREATE TABLE classes
    ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY 
    , name VARCHAR(50) NOT NULL
    , description TINYTEXT NULL
    , parent_class_id INTEGER UNSIGNED NULL
    , INDEX (parent_class_id)
    , FOREIGN KEY (parent_class_id) REFERENCES classes (id)
    ) ENGINE=InnoDB
    ;
    INSERT 
      INTO classes 
    VALUES
         ( 0
         , 'base'
         , 'description blah blah....'
         , NULL )
    ;
    simple, innit

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Right, that's what I figured. It had something to do with referring to the 0 value that hadn't been committed yet. I just didn't know that the foreign key could be a null value. I thought it always had to make a valid reference to another record. Well, that simplified things a whole lot and will actually work out better. Thanks for your time and help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •