SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help setting up InnoDB tables with referential integrity

    Hi there, I'm setting up my database structure for a project I'm just about to start. Having previously used MyISAM, I've decided to use InnoDB for this project due to the advantages offered with true foreign keys.

    However, I don't think I've set up my tables correctly as I'm not allowed to create any data. For example, `pages` is a table which may (but does not necessarily need to) have a visual attached to it, therefore there is a column visual_id in the `pages` table, which checks the `visuals` table. When I try and create some records within pages, I get the following error:
    Code:
    Cannot add or update a child row: a foreign key constraint fails (`alona_hotel/pages`, CONSTRAINT `pages_ibfk_3` FOREIGN KEY (`visual_id`) REFERENCES `visuals` (`id`) ON DELETE NO ACTION)
    They way I have set up both the pages and visuals table is as follows. For situations where a cascaded delete is not necessary, I have set up the foreign key relation and added ON DELETE NO ACTION.

    I'd be grateful for any help here, no doubt it's something idiotic on my part!

    Code:
    -- Table structure for table `pages`
    --
    
    CREATE TABLE `pages` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(250) NOT NULL,
      `url` varchar(250) NOT NULL,
      `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `creator` int(11) NOT NULL default '0',
      `page_id` int(11) NOT NULL default '0',
      `visual_id` int(11) NOT NULL default '0',
      PRIMARY KEY  (`id`),
      KEY `visual_id` (`visual_id`),
      KEY `page_id` (`page_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `visuals`
    --
    
    CREATE TABLE `visuals` (
      `id` int(11) NOT NULL auto_increment,
      `url` varchar(250) NOT NULL,
      `alt_text` varchar(500) NOT NULL,
      `visual_type_id` int(11) NOT NULL,
      PRIMARY KEY  (`id`),
      KEY `visual_type_id` (`visual_type_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
    
    --
    -- Constraints for dumped tables
    --
    
    --
    -- Constraints for table `pages`
    --
    ALTER TABLE `pages`
      ADD CONSTRAINT `pages_ibfk_2` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE NO ACTION,
      ADD CONSTRAINT `pages_ibfk_3` FOREIGN KEY (`visual_id`) REFERENCES `visuals` (`id`) ON DELETE NO ACTION;
    
    --
    -- Constraints for table `visuals`
    --
    ALTER TABLE `visuals`
      ADD CONSTRAINT `visuals_ibfk_1` FOREIGN KEY (`visual_type_id`) REFERENCES `visual_types` (`id`) ON DELETE NO ACTION;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the error message is telling you that it cannot add the row to the pages table because the value you have supplied for the visual_id column is not one of the values in the id column of the visuals table

    you need to add the data to your tables in the correct sequence

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply @r937. Only problem i have here i think is that a page isn't dependent on the visuals table, it may or may not have a visual associated with it. In this type of situation, should the foreign key constraint be removed altogether? Or is my design perhaps flawed?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    make the visual_id optional by declaring it NULL

    you had it as NOT NULL DEFAULT '0', and you cannot stick a 0 in there unless there's a row in the visuals table with a PK of 0 (which is impossible if it's an auto_increment)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah that sorted it, i've obviously had some lazy habits from using MyIsam which I can't get away with now!


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
  •