Originally Posted by br1dil

Hello everyone,

At first, I would like to wish an happy new year for everyone.

Now, my problem! I'm starting a new web app (which is the first one I will completely write) and after modeling the database, I encoutered a problem for adding datas in my category's table:

Help with Code Tags

MySQL Syntax (Toggle Plain Text)

--
-- Structure de la table `categories`
--
 
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) DEFAULT NULL,
  `parent_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `alias` VARCHAR(20) DEFAULT NULL,
  `image` VARCHAR(255) DEFAULT NULL,
  `ordering` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `system` VARCHAR(20) DEFAULT NULL,
  `lft` INT(10) NOT NULL,
  `rght` INT(10) NOT NULL,
  `created` DATETIME NOT NULL,
  `modified` DATETIME NOT NULL,
  `visible` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_categories_categories` (`parent_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=1 ;
 
--
-- Contenu de la table `categories`
--
 
 
--
-- Contraintes pour les tables exportées
--
 
--
-- Contraintes pour la table `categories`
--
ALTER TABLE `categories`
  ADD CONSTRAINT `fk_categories_categories` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -- Structure de la table `categories` -- CREATE TABLE IF NOT EXISTS `categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, `parent_id` int(10) unsigned NOT NULL DEFAULT '0', `alias` varchar(20) DEFAULT NULL, `image` varchar(255) DEFAULT NULL, `ordering` int(10) unsigned NOT NULL DEFAULT '0', `system` varchar(20) DEFAULT NULL, `lft` int(10) NOT NULL, `rght` int(10) NOT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, `visible` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `fk_categories_categories` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=1 ; -- -- Contenu de la table `categories` -- -- -- Contraintes pour les tables exportées -- -- -- Contraintes pour la table `categories` -- ALTER TABLE `categories` ADD CONSTRAINT `fk_categories_categories` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Even if I try to add some entries directly with phpmyadmin, I get the following message:

Help with Code Tags

MySQL Syntax (Toggle Plain Text)

SQL Error: 1452: Cannot add OR UPDATE a child row: a FOREIGN key CONSTRAINT fails (`myapp`.`categories`, CONSTRAINT `fk_categories_categories` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

SQL Error: 1452: Cannot add or update a child row: a foreign key constraint fails (`myapp`.`categories`, CONSTRAINT `fk_categories_categories` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

I'm not quite familiar with the INNODB engine and I assumed this error is linked with the foreign key parent_id. I don't understand why MySQL is sending an error.

I'll be glad if someone has an answer for explaining this.

Thank you guys.

Originally Posted by wilch

hi Br1Dil,

Well from first looks, the last column you added has a foreign key constraint referencing the same table. Is that constraint necessary ? I don't think so. Try commenting it out, or removing it.