Create Foreign Keys Across Multiple Fields in Postgres
What if your foreign keys are more complex than a single field? Postgres lets you enforce foreign key constraints across multiple fields with a similar syntax to defining complex primary keys.
[00:00] We're going to look at creating Multi Foreign Keys. Those are foreign keys across multiple fields. To do this, we're going to create a bunch of tables that we're going to need for owning a movie rental store. The table that we're going to reference, ultimately, is going to be called "Rentable Movies".
[00:17] You can see that we have a bunch of foreign keys already on it. Rentable movies has a unique primary key that represents three different columns. It doesn't have an ID field like all the other ones are. Generally, I do recommend that you use a unique ID field so that you don't have to do complicated joins like this.
[00:39] However, if we do happen to have this Multi Foreign Key, we can still create it on another table that references rentable movies. I'm going to go ahead and paste in the basic definition for the rentings table. This will link a particular rentable movie to a guest who has rented it.
[01:00] Here is where we're actually going to define this new foreign key. We can't just use the word references, because not one specific column references another.
[01:09] We start with foreign key, and then, we say the foreign key is the combination of the movie ID, the store ID and the copy number, because those were the fields that represented the primary key on the other table.
[01:21] References rentable movies. It references the same columns on that table. Copy number. Here, I will also create some mock data for these other tables.
[01:35] I'll take a lookie, what this looks like real quick. We have directors, we have a couple of movies, we have a couple of store locations; San Francisco and Philadelphia, and finally, we have a couple of rentable movies that link up all of these individual things to store IDs and copies that they have in those stores.
[01:56] Here, what's important to point out is that we can't insert a rentable movie renting that doesn't exist. We'll insert into the rentings table. This guy has guest ID, movie ID, store ID, copy number; it has due back, and it has whether or not it was returned.
[02:17] Let's look at our values here. We don't have a movie ID three, store ID one copy one, that doesn't exist. We do have a guest one. Due back will be a day, we'll pick an arbitrary date, way in the past for some reason. It hasn't been returned, we'll try to insert this.
[02:44] This violates the foreign key constraint on the rentable movies table, because we didn't have this combination anywhere. We do however have the combination of movie ID one, and store ID one, and copy number. We insert that and that works fine.
[03:02] We can select all from rentings and see that. In fact, we have created that renting now.