QUESTION:
Constraints and Referencial Integrity
Further modify one or more of your CREATE TABLE statements to include the following referential integrity constraints and policies.
1. Referential integrity from Rating.rID to Reviewer.rID
Reviewers updated: cascade
Reviewers deleted: set null
All others: error
2. Referential integrity from Rating.mID to Movie.mID
Movies deleted: cascade
All others: error
I am unable to determine why an error is produced at line 6. Any thoughts?
SQL Additions Query 1 x B 3 So Limit to 1000 rows 1 /* Delete the tables if they already exist */ - 1 1 Jump to Automatic context help is disabled. Use the toolbar to manually get help for the current caret position or to toggle automatic help. drop table if exists Movie; drop table if exists Reviewer; drop table if exists Rating; 10. /* Create the schema for our tables */ create table Movie (MID int PRIMARY KEY, title VARCHAR(200), year int, director VARCHAR(200), UNIQUE(title, year)); create table Reviewer(rID int PRIMARY KEY, name VARCHAR(200) NOT NULL); create table Rating (rID int NOT NULL, MID int NOT NULL, stars int NOT NULL, ratingDate date, PRIMARY KEY (TID), FOREIGN KEY (CID) REFERENCES REVIEWER (CID)); 14 15. 16 18. 19. /* 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 (184, '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'); 21 . 24. 25 26. 27. 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(297. 'James Cameron'): > Context Help Snippets o Action Output # Time Action 1 13:38:48 drop table if exists Movie 2 13:38:48 drop table if exists Reviewer 3 13:38:49 drop table if exists Rating 4 13:38:49 create table Movie(mID int PRIMARY KEY, title VARCHAR(200). year int, director VARCHAR(200). UNIQUE(title year)) 5 13:38:49 create table Reviewer(rID int PRIMARY KEY, name VARCHAR(200) NOT NULL) 6 13:38:49 create table Rating(rID int NOT NULL, mlDint NOT NULL, stars int NOT NULL, rating Date date, PRIMARY KEY (HID), FOREIGN KEY (PID) REFERENCES REVIEWER (PID)) Message O row(s) affected Orow(s) affected Orow(s) affected, 1 waming(s): 1051 Unknown table Orow(s) affected Orow(s) affected Error Code: 1142. REFERENCES command denied to user .rating' Duration / Fetch 0.063 sec 0.079 sec 0.046 sec 0.047 sec 0.047 sec 0.031 sec @localhost' for table 'reviewer
