How can foreign keys be used to reference genres in a movie database to ensure data consistency in PHP applications?

To ensure data consistency in a movie database, foreign keys can be used to reference genres. This means that each movie record will have a genre_id column that corresponds to a genre record in the genres table. By setting up this relationship, it ensures that only valid genre IDs can be assigned to movies, maintaining data integrity. 

// Create movies table with genre_id as a foreign key
$sql = "CREATE TABLE movies (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    genre_id INT(6) UNSIGNED,
    FOREIGN KEY (genre_id) REFERENCES genres(id)
)";

// Insert a new movie record with a valid genre_id
$sql = "INSERT INTO movies (title, genre_id) VALUES ('The Matrix', 1)";

// Attempt to insert a new movie record with an invalid genre_id
$sql = "INSERT INTO movies (title, genre_id) VALUES ('The Matrix Reloaded', 10)";
// This would result in a foreign key constraint error

Keywords

foreign keys genres movie database data consistency PHP applications

Related Questions