CODEWITHSUNDEEP
mysqlmysql-error-1452
Henry Green
Henry Green

Reputation: 243

I cannot insert values into my table on MySQL

I'm trying to insert these values into the Artist_Name column on one of my tables in my MySQL database

INSERT INTO tbl_Artist (Artist_Name)VALUES ('Taylor Swift');
INSERT INTO tbl_Artist (Artist_Name)VALUES ('Elton John');
INSERT INTO tbl_Artist (Artist_Name)VALUES ('Kanye West');
INSERT INTO tbl_Artist (Artist_Name) VALUES ('Fallout Boy');

But whenever I try to I keep getting this error!

#1452 - Cannot add or update a child row: a foreign key constraint fails
(`b4014107_db2/tbl_Artist`, CONSTRAINT `tbl_Artist_ibfk_1` FOREIGN KEY  
(`Artist_id`) REFERENCES `tbl_Artist` (`Artist_id`)

I'm assuming it's something to do with the foreign key I've assigned onto the Artist_id column in the table itself. I'm just unsure what the issue is and how to rectify is.

I can provide you with the whole code for the table if that helps.

CREATE TABLE `tbl_Artist` (  `Artist_id` int(11) NOT NULL auto_increment,    
`Artist_Name` varchar(32) NOT NULL,  PRIMARY KEY  (`Artist_id`),  CONSTRAINT 
`tbl_Artist_ibfk_1` FOREIGN KEY (`Artist_id`) REFERENCES `tbl_Artist` 
(`Artist_id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

Any help would be greatly appreciated.

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This is your table definition:

CREATE TABLE `tbl_Artist` (
    `Artist_id` int(11) NOT NULL auto_increment,    
    `Artist_Name` varchar(32) NOT NULL, 
    PRIMARY KEY  (`Artist_id`), 
    CONSTRAINT `tbl_Artist_ibfk_1` FOREIGN KEY (`Artist_id`) REFERENCES `tbl_Artist` (`Artist_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

This is actually sort of funny. You have a foreign key reference on the primary key back to the primary key of the table itself. As a consequence, you can never insert rows, because you need a row to refer to.

In any case, a primary key should not be a foreign key back to itself. So, just drop the constraint:

CREATE TABLE `tbl_Artist` (
    `Artist_id` int(11) NOT NULL auto_increment,    
    `Artist_Name` varchar(32) NOT NULL, 
    PRIMARY KEY  (`Artist_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Upvotes: 2

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

Your problem is that foreign key makes that you only insert a row with an ID that already exists on the table. So, the solution is drop this constraint and leave only the primary key. Foreign keys are used to create integrity checks between tables. For example, an album table with a foreign key to the artist table.

Upvotes: 0

Related Questions