Use Foreign Keys to Ensure Data Integrity in Postgres
Every movie needs a director and every rented movie needs to exist in the store. How do we make sure something in another table exists before inserting new data? This lesson will teach us about foreign keys and references.
[00:00] We've created some tables in the past. But, before, we've never explained what this references. This references the directors table. It references the ID column of the directors table, this right here. Those two things are supposed to be the same exact value. That's how we link the two of them. We can go ahead and create that. This will create a foreign key on the movies table.
[00:26] Let's go ahead and insert some directors, as we've done before. That's pretty simple. Then let's also insert into movies. This is going to give us an error this time. That's what we want. We've got our count stars. We've got our director ID. Going to insert some values here. We've inserted some of these in the past, and they go OK.
[00:49] We're going to insert three movies this time, though. Let's give ourselves "Kill Bill." This references director ID one. We know Quentin Tarantino is in the database. That's going to be OK. We're going to try to put in "Funny People." That should go OK as well because we've done this in the past, and we know that we have Judd Apatow. He's in the database as well.
[01:25] Then we're going to insert "Barton Fink." We don't have the Coen brothers in our database here. If I try to reference ID three right here, we know that director ID doesn't exist. This is going to fail because now we have a references column. We say there's no director ID three in the table directors. This is what we want.
[01:52] This going to work wonders for us for making sure that our data is valid. We're going to insert into directors. We'll give the name values. We'll say Coen brothers, insert those. Now, for movies, we can see that Barton Think is in here with director ID three.