CODEWITHSUNDEEP
mysqlforeign-keys
cloude
cloude

Reputation: 346

Add Foreign Key to existing table (error 1451)

I have two tables, with foreign keys, but when delete a record from 'fb_campaign' get follow error:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (mydb.fb_campaign_cat, CONSTRAINT fb_campaign_cat_ibfk_1 FOREIGN KEY (id) REFERENCES fb_campaign (id_cat))

Issue occurs when I add the second foreign key.

table1: fb_campaign_cat

+----+-----------+
| id |   fb_cat  |
+----+-----------+
|  1 | category1 |
|  2 | category2 |
|  3 | category3 |
+----+-----------+

table2: fb_campaign

+-------+--------+-----------+
| id_fb | id_cat |   name    |
+-------+--------+-----------+
|     1 |      1 | campaign1 |
|     2 |      2 | campaign2 |
+-------+--------+-----------+

** schema **

CREATE TABLE `fb_campaign_cat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fb_cat` varchar(30) CHARACTER SET utf8mb4 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `fb_campaign` (
  `id_fb` int(11) NOT NULL AUTO_INCREMENT,
  `id_cat` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

FOREIGN KEY

ALTER TABLE fb_campaign
ADD CONSTRAINT fb_campaign_cat 
FOREIGN KEY (id_cat) 
REFERENCES fb_campaign_cat(id) 
ON DELETE CASCADE;


ALTER TABLE fb_campaign_cat
ADD FOREIGN KEY (id) REFERENCES fb_campaign(id_cat);

Upvotes: 0

Views: 82

Answers (1)

user14402059
user14402059

Reputation:

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

Upvotes: 1

Related Questions