Understanding InnoDB Foreign Keys

Question

I follow one tutorial on PHP and MySQL. At this point I should create a database that should look like this:

I created the databases manually, but I do not understand the point in the roll table. For example, let's say I want to add a movie: how will I do this because I am not allowed? Error example:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`movie`, CONSTRAINT `movie_ibfk_1` FOREIGN KEY (`movieCode`) REFERENCES `roll` (`movieCode`) ON DELETE CASCADE) 

I personally would do something like:

Table artist: artistId , first name, last name, nationality, dateOfBirth, otherInfo Tabletop film: movieCode, name, image, category, description, artistId

Being bold, foreign keys associated with it. However, I do not understand the concept of using a roll table. Can someone clarify this for me since I would like to do it like a textbook pretending to teach?

Database Scheme I still have:

 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE TABLE IF NOT EXISTS `artist` ( `artistId` int(10) unsigned NOT NULL AUTO_INCREMENT, `firstName` char(30) NOT NULL, `lastName` char(30) NOT NULL, `dateOfBirth` date NOT NULL, `nationality` char(30) NOT NULL, `otherInfo` text NOT NULL, PRIMARY KEY (`artistId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `movie` ( `movieCode` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` char(30) NOT NULL, `image` varchar(50) NOT NULL, `category` char(50) NOT NULL, `movieDesc` text NOT NULL, PRIMARY KEY (`movieCode`), UNIQUE KEY `title` (`title`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `roll` ( `movieCode` int(10) unsigned NOT NULL, `artistId` int(10) unsigned NOT NULL, PRIMARY KEY (`movieCode`,`artistId`), KEY `artistId` (`artistId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `user` ( `userId` int(10) unsigned NOT NULL AUTO_INCREMENT, `firstName` char(30) NOT NULL, `lastName` char(30) NOT NULL, `username` char(30) NOT NULL, `password` char(20) NOT NULL, `usertype` int(1) unsigned NOT NULL, PRIMARY KEY (`userId`), UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ALTER TABLE `movie` ADD CONSTRAINT `movie_ibfk_1` FOREIGN KEY (`movieCode`) REFERENCES `roll` (`movieCode`) ON DELETE CASCADE; ALTER TABLE `roll` ADD CONSTRAINT `roll_ibfk_1` FOREIGN KEY (`artistId`) REFERENCES `artist` (`artistId`) ON DELETE CASCADE; 

Answer 1

The roll table (which should probably be called the role table) is known as the many-to-many relationship.

It stores the link between the film and the artist. Since there can be many artists in a film, and an artist can appear in many films, you need a separate table to store all of these relationships. Each line in roll represents an artist who appears in the film.

To avoid any restriction errors, you first need to insert the movie and artist into the database, and then insert the row into the roll table to determine that the artist has appeared in this film. Thus, the roll table needs two foreign key constraints; one in the artist table that you have:

 ALTER TABLE `roll` ADD CONSTRAINT `roll_ibfk_1` FOREIGN KEY (`artistId`) REFERENCES `artist` (`artistId`) ON DELETE CASCADE; 

And in the movie table:

 ALTER TABLE `roll` ADD CONSTRAINT `roll_ibfk_2` FOREIGN KEY (`movieCode`) REFERENCES `movie` (`movieCode`) ON DELETE CASCADE; 

With these restrictions, you cannot determine the role if both this artist and the film do not exist.

Answer 2

Your foreign key on movieCode should be in the roll table. MovieCode is the main key to the movie table.

Answer 3

In this example, you need to generate foreign key constraints from the roll (role) table to other tables (movie, artist), and not vice versa.

This error message

 #1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`movie`, CONSTRAINT `movie_ibfk_1` FOREIGN KEY (`movieCode`) REFERENCES `roll` (`movieCode`) ON DELETE CASCADE) 

implies that you have a foreign key constraint in the movie that references the roll (role) table.

This would mean that semantics can only be introduced for a film for which there is a role (recording in a video).

But I suggest that the semantics should be that you can only add roles to existing films and artist artists. Therefore, the roll (role) table must have foreign keys in other tables, then you can add artists and films, and then enter entries in roles (roles) for valid pairs of movie artists.