Delete row from table:"Cannot delete or update a parent row:a foreign key constraint fails" is that a FOREIGN KEY problemm or CASCADE will do the job?

Question

I want to delete a row from a "prods" table. Whenever I delete a row I want it to delete the rows from other tables associated with it.

Whenever I try to delete a row from "prods" using my PHP code - I get this error:

A Database Error Occurred

Error Number: 1451

Cannot delete or update a parent row: a foreign key constraint fails (`tools`.`keywords`, CONSTRAINT `keywords_ibfk_1` FOREIGN KEY (`key_prod`) REFERENCES `prods` (`prod_id`))

I got the same with the 'keywords' table - this was solved by deleting the rows that are "related" in the "data" table and just then - deleting the row from the 'keywords' table.

But when I encounter this issue again when deleting a row from the 'prods' table - I noticed that this cant be working like this and there have to be a much more efficient way to do this.

After googling a little bit I found out that I can maybe use "DELETE Cascade" - and it might cause problems (or deleting unwanted rows). I really don't know if it will - so I reaserched this a little bit more. Following other search results I have found this post:

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

Where the dude was told that the FOREIGN KEY wasn't done right - and he has to swap between them, which will solve his problem.

I am new to working with complex databases and I wanted to know if I am doing this the right way - all FOREIGN KEYs are done right, and if I use DELETE CASCADE will do the job right.

This is my DB dump structure (MySQL):

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


-- Table structure for table `prods`
--

CREATE TABLE `prods` (
  `prod_id` int(11) NOT NULL,
  `prod_name` varchar(255) NOT NULL,
  `prod_aaa_id` varchar(255) NOT NULL,
  `prod_bbb_id` varchar(255) NOT NULL,
  `prod_get_installs` tinyint(1) NOT NULL,
  `prod_user` int(11) NOT NULL,
  `prod_client_email` varchar(255) NOT NULL,
  `prod_client_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `prod_data`
--

CREATE TABLE `prod_data` (
  `ad_id` int(11) NOT NULL,
  `ad_prod` int(11) NOT NULL,
  `ad_date` date NOT NULL,
  `ad_aaa_inst` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `data`
--

CREATE TABLE `data` (
  `id` int(11) NOT NULL,
  `dat_id` int(11) NOT NULL,
  `dat_date` date NOT NULL,
  `dat_rank_aaa` int(11) NOT NULL,
  `dat_traffic_aaa` float NOT NULL,
  `dat_rank_bbb` int(11) NOT NULL,
  `dat_traffic_bbb` float NOT NULL,
  `dat_difficulty_aaa` float NOT NULL,
  `dat_difficulty_bbb` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------
--
-- Table structure for table `keywords`
--

CREATE TABLE `keywords` (
  `key_id` int(11) NOT NULL,
  `key_word` varchar(255) NOT NULL,
  `key_prod` int(11) NOT NULL,
  `kay_country` text NOT NULL,
  `key_is_wr` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `u_id` int(11) NOT NULL,
  `u_name` varchar(255) NOT NULL,
  `u_email` varchar(255) NOT NULL,
  `u_password` varchar(255) NOT NULL,
  `u_permission` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `weekly_report`
--

CREATE TABLE `weekly_report` (
  `wr_id` int(11) NOT NULL,
  `wr_prod_id` int(11) NOT NULL,
  `wr_date` date NOT NULL,
  `wr_date1` date NOT NULL,
  `wr_date2` date NOT NULL,
  `wr_date3` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `prods`
--
ALTER TABLE `prods`
  ADD PRIMARY KEY (`prod_id`),
  ADD KEY `prod_user` (`prod_user`),
  ADD KEY `prod_user_2` (`prod_user`);

--
-- Indexes for table `prod_data`
--
ALTER TABLE `prod_data`
  ADD PRIMARY KEY (`ad_id`),
  ADD KEY `ad_prod` (`ad_prod`);

--
-- Indexes for table `data`
--
ALTER TABLE `data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `dat_id` (`dat_id`);

--
-- Indexes for table `kas`
--
ALTER TABLE `kas`
  ADD PRIMARY KEY (`kas_id`);

--
-- Indexes for table `keywords`
--
ALTER TABLE `keywords`
  ADD PRIMARY KEY (`key_id`),
  ADD KEY `key_prod` (`key_prod`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`u_id`),
  ADD KEY `u_id` (`u_id`);

--
-- Indexes for table `weekly_report`
--
ALTER TABLE `weekly_report`
  ADD PRIMARY KEY (`wr_id`),
  ADD KEY `wr_prod_id` (`wr_prod_id`),
  ADD KEY `wr_prod_id_2` (`wr_prod_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `prods`
--
ALTER TABLE `prods`
  MODIFY `prod_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;
--
-- AUTO_INCREMENT for table `prod_data`
--
ALTER TABLE `prod_data`
  MODIFY `ad_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
--
-- AUTO_INCREMENT for table `data`
--
ALTER TABLE `data`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3754;
--
--
-- AUTO_INCREMENT for table `keywords`
--
ALTER TABLE `keywords`
  MODIFY `key_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=236;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `u_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `weekly_report`
--
ALTER TABLE `weekly_report`
  MODIFY `wr_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `prods`
--
ALTER TABLE `prods`
  ADD CONSTRAINT `prods_ibfk_1` FOREIGN KEY (`prod_user`) REFERENCES `users` (`u_id`);

--
-- Constraints for table `data`
--
ALTER TABLE `data`
  ADD CONSTRAINT `data_ibfk_1` FOREIGN KEY (`dat_id`) REFERENCES `keywords` (`key_id`);

--
-- Constraints for table `keywords`
--
ALTER TABLE `keywords`
  ADD CONSTRAINT `keywords_ibfk_1` FOREIGN KEY (`key_prod`) REFERENCES `prods` (`prod_id`);

--
-- Constraints for table `weekly_report`
--
ALTER TABLE `weekly_report`
  ADD CONSTRAINT `weekly_report_ibfk_1` FOREIGN KEY (`wr_prod_id`) REFERENCES `prods` (`prod_id`);

Can you please tell me if the FOREIGN KEYs have been done right? of should I change something with my structure?

Will "CASCADE" work in this case without any issues?

Thanks a lot.

EDIT:

Is this what's causing all the problems?

ALTER TABLE `keywords`
  ADD CONSTRAINT `keywords_ibfk_1` FOREIGN KEY (`key_prod`) REFERENCES `prods` (`prod_id`);

If yes, in this current state, with all the data I currently have on my DB, how can I cancel this and flip it around with no errors?

More information about the current structure:

Every 'prod' has more than 1 keywords ('keywords' table).
Every 'keyword' has more than one 'data' row.
Every 'prod' has only one user.
Every 'prod' has more than one 'prod_data'.

So just for being sure, following this short explanation: http://www.w3schools.com/sql/sql_foreignkey.asp

The 'key_prod' is clearly the FOREIGN KEY in the "keywords" table, and the 'prod_id' is the "PRIMARY KEY" in the "prods" table.

I'm very confused right now.

EDIT #2:

This is an other solution I found, not so sure how to apply this in my code, but anyway:

The simple way would be to disable the foreign key check; make the changes then re-enable foreign key check.
SET FOREIGN_KEY_CHECKS=0; -- to disable them
SET FOREIGN_KEY_CHECKS=1; -- to re-enable them

I'm not sure if it's a workaround, or a real solution for this kind of problem, and I really want my code to work "by the book" especially when I'm new to working with this kind of stuff.

score 2 · Accepted Answer · answered May 02 '16 at 14:39

2

I had work with similar database project. First backup your database.

Try to drop your foreign key.

ALTER TABLE table_name DROP FOREIGN KEY column_name_ibfk_1;

After that you can now create a foreign key which can do your above mentioned behaviour.

ALTER TABLE table_name ADD FOREIGN KEY(column_name) REFERENCES table_name(column_name) ON DELETE CASCADE ON UPDATE CASCADE;

answered May 02 '16 at 14:39

nkengbeza

355
5
18

Like this? `ALTER TABLE 'keywords' ADD FOREIGN KEY ('key_prod_ibfk_1') REFERENCES 'prods'('prod_id') ON DELETE CASCADE ON UPDATE CASCADE;` So it's actually like the original one mentioned in the main post but without the `CONSTRAINT` ? – Imnotapotato May 02 '16 at 14:59
What's the difference between the two? I created my FOREIGN KEY using PHPMYADMIN. Why is it different? – Imnotapotato May 02 '16 at 15:10
There's no difference in foreign key constraints, whether it is created using phpmyadmin, or from CLI or others, except you decide to add ON DELETE CASCADE ON UPDATE CASCADE. If you proper created your foreign key, then you can decided on what to do with primary-foreign key fields data manipulation. If you are using phpmyadmin, go to the table containing your foreign key. Click on structure -> Relational View. Scroll down. There you can add foreign keys and their decide on "CASCADE". – nkengbeza May 03 '16 at 11:27

Delete row from table:"Cannot delete or update a parent row:a foreign key constraint fails" is that a FOREIGN KEY problemm or CASCADE will do the job?

1 Answers1