By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 1,905 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Cannot add or update a child row: a foreign key constraint fails

P: 8
Can anyone tell me what i'm doing wrong here?I'm trying to set up a one-to-one relationship between two tables, but it just doesn't work.These are my tables:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE prices
  2. (
  3.     id INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
  4.     price FLOAT NOT NULL
  5.  
  6. ) ENGINE=INNODB ;
  7. CREATE TABLE films
  8. (
  9.     film_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
  10.     name VARCHAR(30) NOT NULL,
  11.     FOREIGN KEY (film_id) REFERENCES  prices (id)
  12.  
  13.  
  14.     ) ENGINE=INNODB ;
  15.  
And this is the insert statement i'm using to populate them(I'm getting the following error:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`video_library`.`films`, CONSTRAINT `films_ibfk_5` FOREIGN KEY (`film_id`) RE
FERENCES `prices` (`id`)))
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO prices (price)
  2. VALUES  (1.99),
  3.         (2.99),
  4.         (3.99),        
  5.         (4.99),
  6.         (5.99),
  7.         (6.99),
  8.         (7.99),
  9.         (8.99),
  10.         (9.99),
  11.         (10.99),
  12.         (11.99),
  13.         (12.99),
  14.         (13.99);
  15.  
  16. INSERT INTO films (name)        
  17. VALUES  ('The Godfather'),
  18.         ('The Godfather II'),
  19.         ('The Shawshank redemption'),
  20.         ('Pulp fiction'),
  21.         ('12 Angry men'),
  22.         ('Schindler\'s List'),
  23.         ('Fight club'),
  24.         ('Inception'),
  25.         ('The green mile'),
  26.         ('Dog Day Afternoon'),
  27.         ('The Dark Knight'),
  28.         ('Saving Private Ryan'),
  29.         ('Se7en');
  30.  
Thanks in advance
Mar 26 '12 #1
Share this Question
Share on Google+
2 Replies


100+
P: 1,059
Really? Godfather is cheaper than Godfatehr II?

you need to rethink the price ;)

anyway:

pointer number one this is not a very smart way to design your database. do two things
1. Remove Auto Increment constraints from the second database.
2 and insert all the the data in table two with proper id taken from table on1
Mar 30 '12 #2

P: 8
Thanks for the help.
Apr 2 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.