title: Understanding InnoDB foreign keys
body: <p>I am following one PHP &amp; MySQL tutorial. At this point, I have to create a database that should look like this:</p>
  
  <p><img src="https://i.stack.imgur.com/0OULz.png" alt="DB Schema"></p>
  
  <p>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:</p>
  
  <pre><code>#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) 
  </code></pre>
  
  <p>I would personally do something like:</p>
  
  <p>Table artist: <strong>artistId</strong>, firstname, lastname, nationality, dateOfBirth, otherInfo
  Table movie: movieCode, title, image, category, description, <strong>artistId</strong></p>
  
  <p>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?</p>
  
  <p>DB schema I have so far:</p>
  
  <pre><code>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;
  </code></pre>