Foreign key constraint fails

Question

brunope 0 Newbie Poster

17 Years Ago

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.

database-design mysql

3 Contributors
5 Replies
1K Views
1 Day Discussion Span
Latest Post 17 Years Ago Latest Post by verruckt24

Dani AI

Generated 10 Months Ago

Short note on what actually caused the failure and how to fix it.

The table had a self-referencing foreign key on parent_id, while the column used a default of 0 for “no parent.” Because there is no category with id = 0, InnoDB refused the insert — the FK requires the parent row to exist. was right that removing the constraint avoids the immediate error (and did that), but you lose integrity checks. ’s point about the update rule is relevant: the FK’s action clauses determine whether updates/deletes are allowed or automatically propagated.

Practical, low-risk approach:

Find any rows whose parent does not exist (this will show offending data):

SELECT c.id, c.parent_id
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id
WHERE c.parent_id IS NOT NULL AND p.id IS NULL;

Fix the data (set top-level categories to NULL, or point to a valid parent), then make the column allow NULL (use NULL for “no parent”) and re-add the FK if you want enforcement. If you need automatic cleanup use ON DELETE SET NULL or ON DELETE CASCADE deliberately — each has different consequences for a nested-set structure.

Schema choice: prefer InnoDB when you intend to keep referential integrity or perform multi-row nested-set updates, because transactions and row-level locking let you update lft/rght safely. MyISAM offers no FK enforcement or transactions. Before dropping/adding constraints, check the table’s FK names with SHOW CREATE TABLE so you remove the correct constraint.

All 5 Replies

wilch 5 Junior Poster in Training

17 Years Ago

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 5 Junior Poster in Training

17 Years Ago

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)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

brunope 0 Newbie Poster · Answer 1 · 2009-01-09T16:16:05+00:00

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 0 Newbie Poster · Answer 2 · 2009-01-09T16:43:39+00:00

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 438 Posting Shark · Answer 3 · 2009-01-10T00:04:13+00:00

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.

Foreign key constraint fails

Dani AI

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers