4

I tried to insert data into the table Bookratings, but I keep getting this error message. Is this because when I try to insert, it creates duplicates in the Bookratings table, which is not allowed by the PRIMARY key constraint?

MySQL Error 1452 - Cannot add or update a child row: a foreign key constraint fails

enter image description here

My actual code:

drop database if exists amazon2;

create database if not exists amazon2;

use amazon2;

create table books(  
  ISBN varchar(13),
  Title varchar(255),
  Author varchar(255),
  Year int(10),
  Puplisher varchar(255),
  ImageURL varchar(255),
  Genre varchar(12),
  unitprice decimal(6,2),
  primary key (ISBN)
); 


  create table users(
    UserID int(11),
    Country varchar(250),
    Age int(11),
    primary key (UserID)
  );


  create table bookratings(
    UserID int(11) not null,
    ISBN varchar(13) not null,
    Rating int(11),
    primary key (UserID, ISBN),
    foreign key (ISBN) references books (ISBN) on delete cascade on update cascade,
    foreign key (UserID) references users (UserID) on delete cascade on update cascade
   );


   create table orders(
     OrderID int(11),
     UserID int(11) not null,
     Year int(10),
     totalpay decimal(6,2),
     primary key (OrderID),
     foreign key (UserID) references users (UserID)
   );


   create table trans(
     OrderID int(11) not null,
     ISBN varchar(13) not null,
     Quantity int(11),
     primary key (OrderID, ISBN),
     foreign key (OrderID) references orders (OrderID),
     foreign key (ISBN) references books (ISBN)
   );

I have to clarify something: Given by the task, I am not allowed to add any other attributes or delete the existing attributes.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Chicken Chowman
  • 67
  • 1
  • 7
  • 1
    Don't link to you code off-site. Put everything someone needs in order to understand your problem in the question. – mhvelplund Dec 05 '17 at 19:39
  • I'm fairly sure your `Users` primary key needs to be `not null`. Also, I'd recommend using an `ID` (`int`) primary key for your `books` table as well; it's possible that your ISBNs could change! Ideally the primary keys should both `auto increment`. – Obsidian Age Dec 05 '17 at 19:40
  • 3
    Please post you actual code itself, not an image of it. It's hard to test with an image :) – Steampunkery Dec 05 '17 at 19:42
  • i have posted it! – Chicken Chowman Dec 05 '17 at 21:10
  • Now please get rid of the redundant image. – philipxy Dec 06 '17 at 11:30
  • @ObsidianAge PK *means* unique not null. – philipxy Dec 06 '17 at 11:32
  • Hi. For god's sake google the error message without your particular names and read a ton of hits & for SO questions read tons of answers. Also see the 'Related' posts on the right of this page, read everything in the manual mention FKs, and google 'more sql FK info'. And read & act on [act] and [mcve]. – philipxy Dec 06 '17 at 11:35

1 Answers1

10

Foreign key constraint violation means the table you're trying to update contains references to some other table, and you're somehow breaking that reference.

If I've got a table Movies

+----------+---------------+
| Movie_ID |  Movie_Name   |
+----------+---------------+
|        1 | Jaws          |
|        2 | Star-Wars     |
|        3 | Jurassic Park |
+----------+---------------+

And a table User_Reviews

+---------+----------+-------+
| User_ID | Movie_ID | Score |
+---------+----------+-------+
|       1 |        1 | 2.5   |
|       2 |        1 | 5     |
|       3 |        2 | 4     |
|       4 |        2 | 3     |
|       5 |        2 | 4.5   |
|       6 |        3 | 5     |
+---------+----------+-------+

In the User_Reviews table, Movie_ID is a foreign key.

I can't have a review with Movie_ID = 10, because that movie doesn't exist in the Movies table. If I did try to do that, I'd get a foreign key constraint error.

Josh Eller
  • 2,045
  • 6
  • 11