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