2

I'm trying to insert this code into my Albums table on my MySQL database

INSERT INTO `Albums` (`Albumid`, `Name`, `Numberoftracks`, `Artistid`,     ]
`Genre`) VALUES (1, "Innuendo", 12, "Queen", "Rock");

But every time I try to I keep getting this error.

1452 - Cannot add or update a child row: a foreign key constraint fails 
(`b4014107_db1/Albums`, CONSTRAINT `Albums_ibfk_1` FOREIGN KEY (`Artistid`) 
REFERENCES `Artist` (`Artistid`))

I know it's something to do with my foreign key within the table, but I need to manually enter the foreign key because it's not auto-incremented.

Here's the tables code. 

CREATE TABLE `Albums` (  `Albumid` int(6) NOT NULL,  `Name` varchar(50) NOT 
NULL,  `Numberoftracks` int(11) NOT NULL,  `Artistid` int(6) NOT NULL,  
`Genre` varchar(50) NOT NULL,  PRIMARY KEY  (`Albumid`),  KEY `Artistid` 
(`Artistid`),  CONSTRAINT `Albums_ibfk_1` FOREIGN KEY (`Artistid`)   
REFERENCES `Artist` (`Artistid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

How do I fix this? I need to input data into the table.

Henry Green
  • 243
  • 3
  • 4
  • 15
  • 1
    Have you added the artist "Queen" on the artist table? Because if not, you should add the artist first otherwise your FK will always be violated. – Walter_Ritzel Mar 21 '16 at 15:33
  • Also, ArtistId is a number and you are trying to add a VARCHAR there. You should add the artist 'Queen' on table artist and get the ArtistId to use in this insert of yours. – Walter_Ritzel Mar 21 '16 at 15:35

2 Answers2

4

artistid is a foreign key in the table Albums. Parent child relationship error comes when you try to insert a foreign key in child table which is not present in the parent table. artistid is not present in your Artist table.

Also, to add the the datatype of artistid is also different.

Taken from mysql docs

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.

It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.

To remove your error, insert the Queens artist in your Artist table first and then you can insert it into Albums table. Also correct the datatype of column artistid.

FallAndLearn
  • 4,035
  • 1
  • 18
  • 24
0

Using foreign keys doesn't mean any implicit auto-incrementation. A foreign key in a table means that a record must already exist in another. see http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html (change version in the url regarding your needs)

Otherwise, make a trigger if you want the artist to be dynamically created.

Hope it'll clarify.

St3an
  • 726
  • 1
  • 6
  • 21