[SOLVED] Help setting up InnoDB tables with referential integrity

[plastik77]

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:

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)

 

The error message above is telling me that I cannot add the row to the pages table because the value I've supplied for the visual_id column is not one of the values in the id column of the visuals table. The 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?

 

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.

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

 

 

[fenway]

Please don't mark anything "solved" without posting the solution for everyone's benefit.

[plastik77]

@fenway - sorry about that.

 

The relationships had been set up correctly, however, the problem was that in the pages table, I had visual_id set up as NOT NULL, default 0. This meant that when I was trying to insert new data into my pages table, i was getting the FK constraint error because it was looking for a corresponding record in the visuals table with id = 0. This record obviously doesn't exist (and never would as it as auto incrementing field), therefore the solution was to set visual_id to NULL.

 

 

 

[fenway]

Thanks for the insight... no worries.