I am trying to add to my database table, but seem to get an error. I get a 1452 Error. Here is what I think is the problem: I think my parent_fk is referring to an id what does not yet exist. At least that is what I understand of the following error:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (assetgallery.ag_asset, CONSTRAINT fk_ag_asset_2 FOREIGN KEY (parent_fk) REFERENCES ag_asset (id) ON DELETE CASCADE ON UPDATE CASCADE)
Say I have a tuple in my database with the id 2. The first tuple:
'2', '8', NULL, NULL, '2', '2015-09-24 09:42:31', 'sabernLogo_0.png', NULL, NULL, NULL, '180', '80', NULL, '1', NULL, NULL, '3', '1'
I am trying to add another with an INSERT statement. I am Inserting without an id, so it looks like this :
Insert into ag_asset
(album_fk, parent_fk, head_fk, status_fk, modified, filename, title, `desc`, `text`, width, height, owner_fk, locked, remarks, group_fk, user_fk, type_fk)
VALUES(6, 0, null, 1, '2015-10-15 15:47:13.0', 'index.png', null, null, null, 215, 234, null, null, null, null, 3, 1);
From what I understand because my id is Autoincrement'ed, not filling this in would result in a freshly made id, being 2.
Here is the create statement
CREATE TABLE `ag_asset` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`album_fk` int(10) DEFAULT NULL,
`parent_fk` int(10) DEFAULT NULL,
`head_fk` int(10) DEFAULT NULL,
`status_fk` int(10) DEFAULT '1',
`modified` datetime NOT NULL,
`filename` varchar(255) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`desc` text,
`text` longtext,
`width` int(10) DEFAULT NULL,
`height` int(10) DEFAULT NULL,
`owner_fk` int(10) DEFAULT NULL,
`locked` int(10) DEFAULT NULL,
`remarks` text,
`group_fk` int(10) DEFAULT NULL,
`user_fk` int(10) DEFAULT NULL,
`type_fk` int(10) DEFAULT '1',
PRIMARY KEY (`id`),
KEY `in_filename` (`filename`(12)),
KEY `in_title` (`title`(12)),
KEY `fk_ag_asset_1` (`album_fk`),
KEY `fk_ag_asset_2` (`parent_fk`),
KEY `fk_ag_asset_3` (`head_fk`),
KEY `fk_ag_asset_4` (`status_fk`),
KEY `fk_ag_asset_5` (`owner_fk`),
KEY `fk_ag_asset_6` (`type_fk`),
CONSTRAINT `fk_ag_asset_1` FOREIGN KEY (`album_fk`) REFERENCES `ag_album` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_ag_asset_2` FOREIGN KEY (`parent_fk`) REFERENCES `ag_asset` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_ag_asset_3` FOREIGN KEY (`head_fk`) REFERENCES `ag_asset` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_ag_asset_4` FOREIGN KEY (`status_fk`) REFERENCES `ag_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_ag_asset_5` FOREIGN KEY (`owner_fk`) REFERENCES `ag_owner` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_ag_asset_6` FOREIGN KEY (`type_fk`) REFERENCES `ag_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
Do I understand the error correctly or am I even looking in the wrong direction?
In foreign key concept whatever value you are trying to insert/update in child table, must exist in its parent/referenced table.
Here you are trying to insert values like 0, NULL etc in columns for those you have created referenced and your master table does not keep these values.
Solution1:
Either first add these values in master table then you can insert in child table.
Solution2:
If you want to add such data in child table forcefully as per your requirement which is not suggested then you can do as-
set foreign_key_checks=0;
Insert statement here...
set foreign_key_checks=1;