MYSQL: Write the following errors in plain English. If there is a cascading chan

Question

MYSQL:

Write the following errors in plain English. If there is a cascading change, describe the change in plain English.

Code:

/* Delete the tables if they already exist */

drop table if exists Movie;

drop table if exists Reviewer;

drop table if exists Rating;

/* Create the schema for our tables */

create table Movie(mID int UNIQUE, title VARCHAR(200), year int, director VARCHAR(200), UNIQUE(title, year));

create table Reviewer(rID int UNIQUE, name VARCHAR(200) NOT NULL);

create table Rating(rID int, mID int, stars int NOT NULL, ratingDate date, UNIQUE(rID, mID, ratingDate),

FOREIGN KEY(rID)

REFERENCES Reviewer(rID)

ON DELETE SET NULL ON UPDATE CASCADE,

FOREIGN KEY (mID)

REFERENCES Movie(mID)

ON DELETE CASCADE);

/* Populate the tables with our data */

insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');

insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');

insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');

insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');

insert into Movie values(105, 'Titanic', 1997, 'James Cameron');

insert into Movie values(106, 'Snow White', 1937, null);

insert into Movie values(107, 'Avatar', 2009, 'James Cameron');

insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

insert into Reviewer values(201, 'Sarah Martinez');

insert into Reviewer values(202, 'Daniel Lewis');

insert into Reviewer values(203, 'Brittany Harris');

insert into Reviewer values(204, 'Mike Anderson');

insert into Reviewer values(205, 'Chris Jackson');

insert into Reviewer values(206, 'Elizabeth Thomas');

insert into Reviewer values(207, 'James Cameron');

insert into Reviewer values(208, 'Ashley White');

insert into Rating values(201, 101, 2, '2011-01-22');

insert into Rating values(201, 101, 4, '2011-01-27');

insert into Rating values(202, 106, 4, null);

insert into Rating values(203, 103, 2, '2011-01-20');

insert into Rating values(203, 108, 4, '2011-01-12');

insert into Rating values(203, 108, 2, '2011-01-30');

insert into Rating values(204, 101, 3, '2011-01-09');

insert into Rating values(205, 103, 3, '2011-01-27');

insert into Rating values(205, 104, 2, '2011-01-22');

insert into Rating values(205, 108, 4, null);

insert into Rating values(206, 107, 3, '2011-01-15');

insert into Rating values(206, 106, 5, '2011-01-19');

insert into Rating values(207, 107, 5, '2011-01-20');

insert into Rating values(208, 104, 3, '2011-01-02');

Errors:

a. insert into Rating values (209, 109, 3, 2001-01-01); myaql insert into Rating values (209, 109, 3,2001-01-01') ERROR 1452 23000) Cannot add or update a child row: a foreign key constraint fails ewer. ("21D.) ON DELETE SET NULL ON UPDATE CASCADE) RatingCONSIRAINI Rating ibfk1 FOREIGN KEY EID REFERENCES Revi b. update Rating set rID= 209 where ri 208; mysql> update Rating set rID209 where ID-208: ERROR 1452 (23000) Cannot add o update a child row: a foreign key constraint fails( ewer rID ON DELETE SET NULL ON UPDTE CASCADE ·"Rating. CONSTRAINT Rating-ibfk-1 . FOREIGN REY (rID*) REFERENCES Revi c. update Rating set ml): mlD+ 1; mysql> update Rating set mID mID 1: ERROR 1452 (23000) Cannot add or update a child row: a foreign key conatraint Iails eID ON DELETE CASCADE) Rating,CoNsTRAINT Rating ibik 2 FOREIGN KEY (ID REFERENCES Novi update Movie set mlD= 109 where mID-108; mysql> update Movie get mID = 109 where mID = 108; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails Movie ID ON DELETE CASCADE) Rating, CONSTRAINT Rating_ibfk 2 FOREIGN KEY ( mID) REFERENCES

Explanation / Answer

a- insert into Rating values(209, 109, 3,'2001-01-01');

in Rating table, rid is a foreign key, which references to the reviewer table. and mid is also a foreign key which references to the movie table.

since, in Reviewer table, there is no rid = 209 and in Movie table, there is no mid = 109, so if we are trying to insert these values in the child table(Rating), which values are not currently present in the parent table(Reviewer and MOvie), so that is why, there is Foreign key constraint violation.

Foreign key constraint says, the values in the child table, must be mapped with value in the parent table.
since, this criteria fails, then we get an error.

b- update Rating set rid = 209, where rid = 208

this command will update value of rid and set it to 209, wherever it finds rid = 208.
since, in Reviewer table, there is no rid = 209, that is why we are getting Foreign key constraint violation.

Foreign key constraint says, the values in the child table, must be mapped with value in the parent table.
since, this criteria fails, then we get an error.

c-update rating set mID = mID + 1

this command will update value of mid by increasing it by 1.
since there are 8 rows in rating table, and mID values are
101,102,103,104,105,106,107,108.
wherever
101 is will be updated to 102
102 will be updated to 103
103 will be updated to 104
104 will be updated to 105
105 will be updated to 106
106 will be updated to 107
107 will be updated to 108
108 will be updated to 109

since, there is no 109 value of mID in Movie table, so in Rating table, on adding this value into the table, foreign key constraint is violated.

d- update MOvie set mID = 109 where mID = 108

this command will update value of mID and set it to 109, wherever it finds mID = 108.
since, in Movie table, there is no mID = 109, that is why we are getting Foreign key constraint violation.

Foreign key constraint says, the values in the child table, must be mapped with value in the parent table.
since, this criteria fails, then we get an error.

If there is anything that you do not understand then please mention it in the comments section.

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.