Foreign key constraint fails

[brunope]

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:

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

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.

[wilch]

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:

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

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.

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.

[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.

else you can specify a second different table in the fk constraint e.g. rather than fk_table1_table1(column) make it fk_table1_table2(column)

[brunope]

Hi wilch, thank you for replying.

I didn't use the foreign key volountary. It was the way MySQL Workbench create the table Categories. The field 'parent_id' is referring to the same table 'categories' to have an hierarchical treelist of categories.
I tried to delete the index but it doesn't work in phpmyadmin, I got this error:

#1025 - Error on rename of '.\myapp\#sql-9cc_12' to '.\myapp\categories' (errno: 150)

So I tried to delete the table but I got this error too:

#1217 - Cannot delete or update a parent row: a foreign key constraint fails

Why do I have this error? How can I get around it?
This table will contain all the categories used in my app, I will use the adjacency list model and the nested set model too. Is it better to use INNODB than Myisam? What do you think?

Thanks

[brunope]

Ok, I finally deleted the constraint.
First, I had to delete the foreign key:

ALTER TABLE `categories` DROP FOREIGN KEY `fk_categories_categories`;

and after the index key:

ALTER TABLE `categories` DROP INDEX `fk_categories_categories`

So now, I don't have any constraint in my table and I can add datas but I'm wondering if I should use foreign key constraint, is it worth it?

[verruckt24]

While specifying the foreign key constraint you have specified "ON UPDATE NO ACTION" while this seems to mean that no action would be taken on update, it specifically means that you will not be allowed to update too, either remove the clause or if it is binding for you to have it, modify it suitably.