Professional Web Applications Themes

foreign key problem - MySQL

I am not sure how to set up the foreign keys on this one, as I've never done foreign keys before (and I'm sure it shows). I have a tree of information. I can insert into the categories table, but not anything else - I get foreign key errors (and I am not sure how to interpret the error). Cannot add or update a child row: a foreign key constraint fails (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE) I am also curious if the foreign key definition in subcategories would cause the database to fail ...

  1. #1

    Default foreign key problem

    I am not sure how to set up the foreign keys on this one, as I've never done
    foreign keys before (and I'm sure it shows). I have a tree of information.
    I can insert into the categories table, but not anything else - I get
    foreign key errors (and I am not sure how to interpret the error).
    Cannot add or update a child row: a foreign key constraint fails
    (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
    (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)


    I am also curious if the foreign key definition in subcategories would cause
    the database to fail or error on a delete because of the recursion. (that's
    where the tree is stored).


    CREATE TABLE `categories` (
    `cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category here',
    PRIMARY KEY(`cat_id`),
    UNIQUE KEY `ix_ccat_id` (`cat_id`),
    INDEX `ix_ccat`(`category`)
    ) ENGINE=InnoDB;

    CREATE TABLE `subcategories` (
    `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
    `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
    categories table',
    `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
    subcategory',
    FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
    CASCADE,
    FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
    CASCADE,
    PRIMARY KEY(`sub_id`),
    INDEX `ix_sccat_id`(`cat_id`),
    INDEX `ix_scsubcat_id`(`subcat_id`)
    ) ENGINE=InnoDB;

    CREATE TABLE `categoryroots` (
    `root_id` int(10) unsigned NOT NULL auto_increment COMMENT 'ignore',
    `cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id
    from categories table',
    PRIMARY KEY (`root_id`),
    FOREIGN KEY (`cat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
    CASCADE,
    UNIQUE KEY `ix_crcat_id` (`cat_id`)
    ) ENGINE=InnoDB;

    /*
    c
    f-b
    g |
    h |
    i-c-a
    j |
    k |
    l-d
    m
    ----n
    */
    INSERT INTO categories(cat_id,category) VALUES
    (1,'a'),
    (2,'b'),
    (3,'c'),
    (4,'d'),
    (5,'e'),
    (6,'f'),
    (7,'g'),
    (8,'h'),
    (9,'i'),
    (10,'j'),
    (11,'k'),
    (12,'l'),
    (13,'m'),
    (14,'n');

    INSERT INTO categoryroots(cat_id) VALUES
    (1),
    (14);

    INSERT INTO subcategories(cat_id,subcat_id) VALUES
    (1,2),
    (1,3),
    (1,4),
    (2,5),
    (2,6),
    (2,7),
    (3,8),
    (3,9),
    (3,10),
    (4,11),
    (4,12),
    (4,13);




    Jim Michaels Guest

  2. #2

    Default Re: foreign key problem

    Jim Michaels wrote:
    > I am not sure how to set up the foreign keys on this one, as I've never done
    > foreign keys before (and I'm sure it shows). I have a tree of information.
    > I can insert into the categories table, but not anything else - I get
    > foreign key errors (and I am not sure how to interpret the error).
    > Cannot add or update a child row: a foreign key constraint fails
    > (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
    > (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)
    >
    >
    > I am also curious if the foreign key definition in subcategories would cause
    > the database to fail or error on a delete because of the recursion. (that's
    > where the tree is stored).
    >
    >
    > CREATE TABLE `categories` (
    > `cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    > `category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category here',
    > PRIMARY KEY(`cat_id`),
    > UNIQUE KEY `ix_ccat_id` (`cat_id`),
    > INDEX `ix_ccat`(`category`)
    > ) ENGINE=InnoDB;
    >
    > CREATE TABLE `subcategories` (
    > `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
    > `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
    > categories table',
    > `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
    > subcategory',
    > FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
    > CASCADE,
    > FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
    > CASCADE,
    > PRIMARY KEY(`sub_id`),
    > INDEX `ix_sccat_id`(`cat_id`),
    > INDEX `ix_scsubcat_id`(`subcat_id`)
    > ) ENGINE=InnoDB;
    >
    > CREATE TABLE `categoryroots` (
    > `root_id` int(10) unsigned NOT NULL auto_increment COMMENT 'ignore',
    > `cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id
    > from categories table',
    > PRIMARY KEY (`root_id`),
    > FOREIGN KEY (`cat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
    > CASCADE,
    > UNIQUE KEY `ix_crcat_id` (`cat_id`)
    > ) ENGINE=InnoDB;
    >
    (data snipped)

    Jim,

    Your problem is right here:

    FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
    DELETE CASCADE,

    Generally you don't use a foreign key to reference the same table. If I
    understand what you're trying to do, referencing categories should be
    sufficient.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: foreign key problem

    Jerry Stuckle <jstucklexattglobal.net> wrote:
    > Jim Michaels wrote:
    >>
    >> CREATE TABLE `subcategories` (
    >> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    >> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    >> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    >> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE,
    >> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE CASCADE,
    >> PRIMARY KEY(`sub_id`),
    >> INDEX `ix_sccat_id`(`cat_id`),
    >> INDEX `ix_scsubcat_id`(`subcat_id`)
    >> ) ENGINE=InnoDB;
    > Your problem is right here:
    >
    > FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
    > DELETE CASCADE,
    >
    > Generally you don't use a foreign key to reference the same table.
    Although this design looks a bit weird, a foreign key into the same
    table is no problem with InnoDB. In fact it is very useful if you
    want to hold simple parent<->child relation in a single table.


    XL
    Axel Schwenke Guest

  4. #4

    Default Re: foreign key problem

    "Jim Michaels" <jmichae3> wrote in message
    news:5rOdnSBK5PrqbkrenZ2dnUVZ_sydnZ2dcomcast.com. ..
    > Cannot add or update a child row: a foreign key constraint fails
    > (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
    > (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)
    You can't add to a table a reference to the same table if the table hasn't
    been created yet.
    This is true if the SQL interpreter is just parsing the CREATE TABLE
    statement.

    To make self-referencing foreign keys, you have to create the table without
    that foreign key constraint (but do create the field to be used as the
    reference), then use ALTER TABLE to add the constraint.

    For example:

    CREATE TABLE `subcategories` (
    `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
    `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
    categories table',
    `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
    subcategory',
    FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
    CASCADE,
    PRIMARY KEY(`sub_id`),
    INDEX `ix_sccat_id`(`cat_id`),
    INDEX `ix_scsubcat_id`(`subcat_id`)
    ) ENGINE=InnoDB;

    ALTER TABLE `subcategories` ADD CONSTRAINT
    FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`)
    ON DELETE CASCADE;

    Regards,
    Bill K.


    Bill Karwin Guest

  5. #5

    Default Re: foreign key problem

    Axel Schwenke wrote:
    > Jerry Stuckle <jstucklexattglobal.net> wrote:
    >
    >>Jim Michaels wrote:
    >>
    >>>CREATE TABLE `subcategories` (
    >>> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    >>> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    >>> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    >>> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE,
    >>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE CASCADE,
    >>> PRIMARY KEY(`sub_id`),
    >>> INDEX `ix_sccat_id`(`cat_id`),
    >>> INDEX `ix_scsubcat_id`(`subcat_id`)
    >>>) ENGINE=InnoDB;
    >
    >
    >>Your problem is right here:
    >>
    >> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
    >>DELETE CASCADE,
    >>
    >>Generally you don't use a foreign key to reference the same table.
    >
    >
    > Although this design looks a bit weird, a foreign key into the same
    > table is no problem with InnoDB. In fact it is very useful if you
    > want to hold simple parent<->child relation in a single table.
    >
    >
    > XL
    To some extent that's true. However, it also runs into the problem you
    see here. He can't insert because the foreign key doesn't exist yet.

    I should have added the solution is you need to remove the constraint,
    add the first element, then add the constraint back in.

    And BTW - although it might work sometimes, it's generally frowned upon
    to reference you own table for this very reason. Another way to do it
    is to have a second table with the parent-child relationships.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  6. #6

    Default Re: foreign key problem

    > >>>CREATE TABLE `subcategories` (
    > >>> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    > >>> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    > >>> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    > >>> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
    CASCADE,
    > >>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
    DELETE CASCADE,
    > >>> PRIMARY KEY(`sub_id`),
    > >>> INDEX `ix_sccat_id`(`cat_id`),
    > >>> INDEX `ix_scsubcat_id`(`subcat_id`)
    > >>>) ENGINE=InnoDB;
    > >
    > >
    > >>Your problem is right here:
    > >>
    > >> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
    > >>DELETE CASCADE,
    > >>
    > >>Generally you don't use a foreign key to reference the same table.
    > >
    > >
    > > Although this design looks a bit weird, a foreign key into the same
    > > table is no problem with InnoDB. In fact it is very useful if you
    > > want to hold simple parent<->child relation in a single table.
    > >
    > >
    > > XL
    >
    > To some extent that's true. However, it also runs into the problem you
    > see here. He can't insert because the foreign key doesn't exist yet.
    >
    > I should have added the solution is you need to remove the constraint,
    > add the first element, then add the constraint back in.
    Right. That sounds bad -> modifying metadata to get your data in.

    Why not insert a NULL instead and update your inserted row when
    you've got your FK data in order?

    Oh right, now I see:
    CREATE TABLE `subcategories` (
    `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
    `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
    categories table',
    `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
    subcategory',
    FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
    CASCADE,
    FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
    CASCADE,
    PRIMARY KEY(`sub_id`),
    INDEX `ix_sccat_id`(`cat_id`),
    INDEX `ix_scsubcat_id`(`subcat_id`)
    ) ENGINE=InnoDB;


    "subcat_id" is NOT NULL? How can that be? A sub-category
    ALWAYS has to refer to a sub-category? The "default 0" looks
    like there's a dummy sub category. That sounds bad as well.

    IMO, the better thing to do is modify the design and split up this
    table.


    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
    Server
    Upscene Productions
    [url]http://www.upscene.com[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

  7. #7

    Default Re: foreign key problem

    Jerry Stuckle <jstucklexattglobal.net> wrote:
    > Axel Schwenke wrote:
    >>>Jim Michaels wrote:
    >>>Your problem is right here:
    >>>
    >>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
    >>>DELETE CASCADE,
    >>>
    >>>Generally you don't use a foreign key to reference the same table.
    >>
    >>
    >> Although this design looks a bit weird, a foreign key into the same
    >> table is no problem with InnoDB. In fact it is very useful if you
    >> want to hold simple parent<->child relation in a single table.
    > To some extent that's true. However, it also runs into the problem you
    > see here. He can't insert because the foreign key doesn't exist yet.
    That's why I called his design 'weird'. Typically one has no NOT NULL
    constraint on the parent id column. So root nodes are inserted with
    parent id = NULL.

    The other weird point is, that he has a FOREIGN KEY into another table
    *and* a FOREIGN KEY into the same table. Usually you have only one of
    them. If your nodes are all the same, you put them all into one table.
    If some nodes (say: root nodes) are different, you put them in another
    table.


    XL
    Axel Schwenke Guest

  8. #8

    Default Re: foreign key problem

    I removed the offending foreign key. I could not insert the data into
    categoryroots. foreign key problem again. forced to remove the foreign key
    from categoryroots table so I could. why?

    "Jerry Stuckle" <jstucklexattglobal.net> wrote in message
    news:hKGdnZyCgtS1akrenZ2dnUVZ_tqdnZ2dcomcast.com. ..
    > Jim Michaels wrote:
    >> I am not sure how to set up the foreign keys on this one, as I've never
    >> done
    >> foreign keys before (and I'm sure it shows). I have a tree of
    >> information.
    >> I can insert into the categories table, but not anything else - I get
    >> foreign key errors (and I am not sure how to interpret the error).
    >> Cannot add or update a child row: a foreign key constraint fails
    >> (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
    >> (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)
    >>
    >>
    >> I am also curious if the foreign key definition in subcategories would
    >> cause
    >> the database to fail or error on a delete because of the recursion.
    >> (that's
    >> where the tree is stored).
    >>
    >>
    >> CREATE TABLE `categories` (
    >> `cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    >> `category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category
    >> here',
    >> PRIMARY KEY(`cat_id`),
    >> UNIQUE KEY `ix_ccat_id` (`cat_id`),
    >> INDEX `ix_ccat`(`category`)
    >> ) ENGINE=InnoDB;
    >>
    >> CREATE TABLE `subcategories` (
    >> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
    >> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id
    >> from
    >> categories table',
    >> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
    >> subcategory',
    >> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
    >> CASCADE,
    >> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
    >> DELETE
    >> CASCADE,
    >> PRIMARY KEY(`sub_id`),
    >> INDEX `ix_sccat_id`(`cat_id`),
    >> INDEX `ix_scsubcat_id`(`subcat_id`)
    >> ) ENGINE=InnoDB;
    >>
    >> CREATE TABLE `categoryroots` (
    >> `root_id` int(10) unsigned NOT NULL auto_increment COMMENT 'ignore',
    >> `cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id
    >> from categories table',
    >> PRIMARY KEY (`root_id`),
    >> FOREIGN KEY (`cat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
    >> CASCADE,
    >> UNIQUE KEY `ix_crcat_id` (`cat_id`)
    >> ) ENGINE=InnoDB;
    >>
    >
    > (data snipped)
    >
    > Jim,
    >
    > Your problem is right here:
    >
    > FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
    > CASCADE,
    >
    > Generally you don't use a foreign key to reference the same table. If I
    > understand what you're trying to do, referencing categories should be
    > sufficient.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > [email]jstucklexattglobal.net[/email]
    > ==================

    Jim Michaels Guest

  9. #9

    Default Re: foreign key problem

    but then I can't insert any data.

    "Bill Karwin" <billkarwin.com> wrote in message
    news:dr9u620499enews4.newsguy.com...
    > "Jim Michaels" <jmichae3> wrote in message
    > news:5rOdnSBK5PrqbkrenZ2dnUVZ_sydnZ2dcomcast.com. ..
    >> Cannot add or update a child row: a foreign key constraint fails
    >> (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
    >> (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)
    >
    > You can't add to a table a reference to the same table if the table hasn't
    > been created yet.
    > This is true if the SQL interpreter is just parsing the CREATE TABLE
    > statement.
    >
    > To make self-referencing foreign keys, you have to create the table
    > without that foreign key constraint (but do create the field to be used as
    > the reference), then use ALTER TABLE to add the constraint.
    >
    > For example:
    >
    > CREATE TABLE `subcategories` (
    > `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
    > `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
    > categories table',
    > `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
    > subcategory',
    > FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
    > CASCADE,
    > PRIMARY KEY(`sub_id`),
    > INDEX `ix_sccat_id`(`cat_id`),
    > INDEX `ix_scsubcat_id`(`subcat_id`)
    > ) ENGINE=InnoDB;
    >
    > ALTER TABLE `subcategories` ADD CONSTRAINT
    > FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`)
    > ON DELETE CASCADE;
    >
    > Regards,
    > Bill K.
    >

    Jim Michaels Guest

  10. #10

    Default Re: foreign key problem

    Jim Michaels wrote:
    > I removed the offending foreign key. I could not insert the data into
    > categoryroots. foreign key problem again. forced to remove the foreign key
    > from categoryroots table so I could. why?
    >
    Jim,

    When using foreign keys, the item must exist in the referenced table
    before you can add it to the current table.

    In this case it means to add an item to categoryroots with a cat_id of
    1, you need to first have an entry with cat_id of 1 in the subcategories
    table.

    And please don't top post.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  11. #11

    Default Re: foreign key problem

    "Jerry Stuckle" <jstucklexattglobal.net> wrote in message
    news:7oSdnUGMXuioiUfenZ2dnUVZ_vidnZ2dcomcast.com. ..
    > Jim Michaels wrote:
    >> I removed the offending foreign key. I could not insert the data into
    >> categoryroots. foreign key problem again. forced to remove the foreign
    >> key from categoryroots table so I could. why?
    >>
    >
    > Jim,
    >
    > When using foreign keys, the item must exist in the referenced table
    > before you can add it to the current table.
    >
    > In this case it means to add an item to categoryroots with a cat_id of 1,
    > you need to first have an entry with cat_id of 1 in the subcategories
    > table.

    this helped me fix the problem with my tree.
    CREATE TABLE `jmichae3`.`categoryroots` (
    `root_id` int(10) unsigned NOT NULL auto_increment COMMENT 'ignore',
    `cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id
    from categories table',
    PRIMARY KEY (`root_id`),
    FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE
    ON UPDATE NO ACTION,
    UNIQUE KEY `ix_crcat_id` (`cat_id`)
    ) ENGINE=InnoDB;

    REFERENCES categories replaced REFERENCES subcategories. a tree root
    doesn't necessarily have subcategories.
    now I can insert my data. into categoryroots, and it works. it was the 14
    that was messing things up - it's not in subcategories.

    >
    > And please don't top post.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > [email]jstucklexattglobal.net[/email]
    > ==================

    Jim Michaels Guest

  12. #12

    Default Re: foreign key problem


    "Axel Schwenke" <axel.schwenkegmx.de> wrote in message
    news:l1sard.t05.lnidefix.xl.local...
    > Jerry Stuckle <jstucklexattglobal.net> wrote:
    >> Axel Schwenke wrote:
    >>>>Jim Michaels wrote:
    >
    >>>>Your problem is right here:
    >>>>
    >>>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
    >>>>DELETE CASCADE,
    >>>>
    >>>>Generally you don't use a foreign key to reference the same table.
    >>>
    >>>
    >>> Although this design looks a bit weird, a foreign key into the same
    >>> table is no problem with InnoDB. In fact it is very useful if you
    >>> want to hold simple parent<->child relation in a single table.
    >
    >
    >> To some extent that's true. However, it also runs into the problem you
    >> see here. He can't insert because the foreign key doesn't exist yet.
    >
    > That's why I called his design 'weird'. Typically one has no NOT NULL
    > constraint on the parent id column. So root nodes are inserted with
    > parent id = NULL.
    >
    > The other weird point is, that he has a FOREIGN KEY into another table
    > *and* a FOREIGN KEY into the same table. Usually you have only one of
    > them. If your nodes are all the same, you put them all into one table.
    > If some nodes (say: root nodes) are different, you put them in another
    > table.
    >
    that's great until you have multiple roots. it's not necessary to insert a
    subcategory in the subcategories table if there isn't one. I suppose I
    could keep all the roots in this table and query for them. but it makes tree
    traversing slightly more difficult checking for nulls, which in PHP come out
    to be 0's anyway, don't they?
    >
    > XL

    Jim Michaels Guest

  13. Moderated Post

    Default Re: foreign key problem

    Removed by Administrator
    Axel Schwenke Guest
    Moderated Post

  14. #14

    Default Foreign Key problem

    Hi,

    I have a database which has many tables, but my question relates to
    these 4 tables: USER, CONTACT, PHONE, EVENT.

    USER has a 1 to many relationship with CONTACT. CONTACT in turn has 1
    to many relationship with 2 tables PHONE and EVENT. PK of CONTACT is
    supposed to be the FK of both EVENT and PHONE. I can create the
    relationship successfully between CONTACT and either one of these
    tables but not both. If I make the relationship between, say PHONE,
    then I cannot create the FK in EVENT and vice-versa.
    I get Error 1005 and 150. I have also pasted the error report at the
    end.

    I am new to databases so it might be some fundamental database design
    issue but I can't figure it. Would be really grateful if somebody
    could explain it to me.

    The SQL scripts are pasted below:

    DROP TABLE IF EXISTS `my_address_book`.`user`;
    CREATE TABLE `my_address_book`.`user` (
    `UserId` int(10) unsigned NOT NULL auto_increment,
    `FirstName` varchar(45) NOT NULL,
    `LastName` varchar(45) NOT NULL,
    PRIMARY KEY (`UserId`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    DROP TABLE IF EXISTS `my_address_book`.`contact`;
    CREATE TABLE `my_address_book`.`contact` (
    `ContactId` int(10) unsigned NOT NULL auto_increment,
    `UserId` int(10) unsigned NOT NULL,
    `Title` varchar(45) NOT NULL,
    `FirstName` varchar(45) NOT NULL,
    `LastName` varchar(45) NOT NULL,
    PRIMARY KEY (`ContactId`),
    KEY `UserId` (`UserId`),
    CONSTRAINT `UserId` FOREIGN KEY (`UserId`) REFERENCES `user`
    (`UserId`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    DROP TABLE IF EXISTS `my_address_book`.`phone`;
    CREATE TABLE `my_address_book`.`phone` (
    `PhoneId` int(10) unsigned NOT NULL auto_increment,
    `ContactId` int(10) unsigned NOT NULL,
    `PhoneNo` int(10) unsigned NOT NULL,
    `PhoneExt` int(10) unsigned NOT NULL,
    `PhoneTypeId` int(10) unsigned NOT NULL,
    PRIMARY KEY (`PhoneId`),
    KEY `ContactId` (`ContactId`),
    KEY `PhoneTypeId` (`PhoneTypeId`),
    CONSTRAINT `PhoneTypeId` FOREIGN KEY (`PhoneTypeId`) REFERENCES
    `phone_type` (`PhoneTypeId`),
    CONSTRAINT `ContactId` FOREIGN KEY (`ContactId`) REFERENCES `contact`
    (`ContactId`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    DROP TABLE IF EXISTS `my_address_book`.`event`;
    CREATE TABLE `my_address_book`.`event` (
    `EventId` int(10) unsigned NOT NULL auto_increment,
    `ContactId` int(10) unsigned NOT NULL,
    `EventTypeId` int(10) unsigned NOT NULL,
    `EventDate` datetime NOT NULL,
    `EventName` varchar(45) NOT NULL,
    `EventPlace` varchar(45) NOT NULL,
    PRIMARY KEY (`EventId`),
    KEY `EventTypeId` (`EventTypeId`),
    CONSTRAINT `EventTypeId` FOREIGN KEY (`EventTypeId`) REFERENCES
    `event_type` (`EventTypeId`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    In this EVENT table when I try to make 'ContactId' a FK, I get the
    following error:
    Error while executing query

    ALTER TABLE `my_address_book`.`event`
    ADD CONSTRAINT `ContactId`
    FOREIGN KEY `ContactId`(ContactId`)
    REFERENCES `contact`(`ContactId`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

    MySQL Error Number 1005
    Can't create table'.\my_address_book\#sql-b10_1.frm'(errno:121)


    THANKS in advance.
    Ros

    roohbir Guest

  15. #15

    Default foreign key problem

    Hi,

    I have a database which has many tables, but my question relates to
    these 4 tables: USER, CONTACT, PHONE, EVENT.

    USER has a 1 to many relationship with CONTACT. CONTACT in turn has 1
    to many relationship with 2 tables PHONE and EVENT. PK of CONTACT is
    supposed to be the FK of both EVENT and PHONE. I can create the
    relationship successfully between CONTACT and either one of these
    tables but not both. If I make the relationship between, say PHONE,
    then I cannot create the FK in EVENT and vice-versa.
    I get Error 1005 and 150. I have also pasted the error report at the
    end.

    I am new to databases so it might be some fundamental database design
    issue but I can't figure it. Would be really grateful if somebody
    could explain it to me.

    The SQL scripts are pasted below:

    DROP TABLE IF EXISTS `my_address_book`.`user`;
    CREATE TABLE `my_address_book`.`user` (
    `UserId` int(10) unsigned NOT NULL auto_increment,
    `FirstName` varchar(45) NOT NULL,
    `LastName` varchar(45) NOT NULL,
    PRIMARY KEY (`UserId`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    DROP TABLE IF EXISTS `my_address_book`.`contact`;
    CREATE TABLE `my_address_book`.`contact` (
    `ContactId` int(10) unsigned NOT NULL auto_increment,
    `UserId` int(10) unsigned NOT NULL,
    `Title` varchar(45) NOT NULL,
    `FirstName` varchar(45) NOT NULL,
    `LastName` varchar(45) NOT NULL,
    PRIMARY KEY (`ContactId`),
    KEY `UserId` (`UserId`),
    CONSTRAINT `UserId` FOREIGN KEY (`UserId`) REFERENCES `user`
    (`UserId`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    DROP TABLE IF EXISTS `my_address_book`.`phone`;
    CREATE TABLE `my_address_book`.`phone` (
    `PhoneId` int(10) unsigned NOT NULL auto_increment,
    `ContactId` int(10) unsigned NOT NULL,
    `PhoneNo` int(10) unsigned NOT NULL,
    `PhoneExt` int(10) unsigned NOT NULL,
    `PhoneTypeId` int(10) unsigned NOT NULL,
    PRIMARY KEY (`PhoneId`),
    KEY `ContactId` (`ContactId`),
    KEY `PhoneTypeId` (`PhoneTypeId`),
    CONSTRAINT `PhoneTypeId` FOREIGN KEY (`PhoneTypeId`) REFERENCES
    `phone_type` (`PhoneTypeId`),
    CONSTRAINT `ContactId` FOREIGN KEY (`ContactId`) REFERENCES `contact`
    (`ContactId`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    DROP TABLE IF EXISTS `my_address_book`.`event`;
    CREATE TABLE `my_address_book`.`event` (
    `EventId` int(10) unsigned NOT NULL auto_increment,
    `ContactId` int(10) unsigned NOT NULL,
    `EventTypeId` int(10) unsigned NOT NULL,
    `EventDate` datetime NOT NULL,
    `EventName` varchar(45) NOT NULL,
    `EventPlace` varchar(45) NOT NULL,
    PRIMARY KEY (`EventId`),
    KEY `EventTypeId` (`EventTypeId`),
    CONSTRAINT `EventTypeId` FOREIGN KEY (`EventTypeId`) REFERENCES
    `event_type` (`EventTypeId`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    In this EVENT table when I try to make 'ContactId' a FK, I get the
    following error:
    Error while executing query

    ALTER TABLE `my_address_book`.`event`
    ADD CONSTRAINT `ContactId`
    FOREIGN KEY `ContactId`(`ContactId`)
    REFERENCES `contact`(`ContactId`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

    MySQL Error Number 1005
    Can't create table'.\my_address_book\#sql-b10_1.frm'(errno:121)

    THANKS in advance.
    Ros

    roohbir Guest

  16. #16

    Default Re: Foreign Key problem

    roohbir wrote: 

    You already have a constraint named 'ContactId'. Constraint names, if
    supplied, must be unique.

    I usually make my constraint names based on both table names (and a
    column name if necessary).

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Foreign key
    By Bruno Guerpillon in forum MySQL
    Replies: 6
    Last Post: November 24th, 07:29 AM
  2. Foreign keys tut
    By frizzle in forum MySQL
    Replies: 2
    Last Post: July 18th, 05:45 PM
  3. Serial Foreign Key
    By Jonathan Stafford in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 7th, 06:37 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139