Understanding InnoDB foreign keys

Question

I am following one PHP & MySQL tutorial. At this point, I have to create a database that should look like this:

I created the databases by hand, but I do not understand the point of the roll table. For instance, let's say I want to add a movie: how am I going to do it, since I am not allowed? Example error:

#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 would personally do something like:

Table artist: artistId, firstname, lastname, nationality, dateOfBirth, otherInfo Table movie: movieCode, title, image, category, description, artistId

Being the one in bold the related foreign keys. However, I do not understand the concept of using the roll table there. Can someone clarify this for me, as I'd like to do it like the tutorial pretends to teach?

DB schema I have so far:

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 a role table) is what's known as a many-to-many relationship.

It stores a link between a movie and an artist. Since a movie can have many artists, and an artist can appear in many movies, you need a separate table to store all those relationships. Each row in roll represents an artist appearing in a movie.

To avoid any constraint errors, you would need to insert a movie and an artist in the database first, and then insert a row into the roll table to define that artist appeared in that movie. So, the roll table would need two foreign key constraints; one on the artist table, which you have:

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

And another on the movie table:

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

With these constraints, you won't be able to define a role unless both that artist and movie exist.