Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In a relational data base, it's common to have one table refer to another table by its primary key. Problems can arise however if the reference points to a key that doesn't exist. The foreign key constraint is designed to prevent this from happening. We'll be using the scratch database for this lesson and again I'm going to copy from the exercise files. Because well, in this case it actually happens to be a very significant amount of typing.
And the reason for this is that in order to demonstrate the foreign key constraint, we really need to have several tables with different purposes and have them. Interconnected with a few rows of data. And so we have three tables here, a client table, a book table, and a lend table. So the client table just really has a name for the client and an ID. You notice it's integer, AUTO_INCREMENT PRIMARY KEY. And a book table for the books that are going to be lent to the clients, with the title of the book and an ID, very simple tables.
And a lend table and this is what's called a junction table in relational database terminology. The lend table itself has its own ID and it has a time stamp for when the lending happens. And it has. ID tables for the client and the book tables. So c_id is for the client table and b_id is for the book table. And so when we lend a book then a row gets added to the lend table showing the client and the book.
Where the lending transaction and that's called a junction table and of course you don't want to have a book lent to a client that doesn't exist. And you'll notice here that we have three clients, one, two and three because they're auto increment. And three books, one, two, and three. And you'll notice that client number two is being lent book number five and that should not be allowed. And in fact, the foreign key constraint is designed to prevent this. So let's go ahead and we'll copy and paste this into our SQL box and.
I'm going to go ahead and I'm just going to press Go here. And you'll see that here's our client table, Freddy, Karen and Harry, one, two and three. Here's our books. These are actually books that I like. These are some of my favorite books. And here is the lend table. This is our junction table. And you notice that there's, all the time stamps are the same because we inserted all those rows at once. And we have client one borrowed two books, book number one and two. Client three borrowed book number three. And client two borrowed book number five, which does not exist.
And this is this joint query down here. Which you might use a query like this to look at your entire picture and you notice it uses left joins. We'll learn a little more about left joins in a later chapter, but all this is doing here is it's looking at the junction table and it's bringing in the names from the client and the book, tables. So we have the lend id, the lend stamp, the client name, and the book title and those are all joined together.
So you'll notice that here, Karen, that's client number two. Up here client number two is Karen. And she borrowed book number five which does not exist and so there's a NULL here and that's what it is that we want to prevent. And that actually happens here in this INSERT INTO lend. Right there in that query. INSERT INTO lend, VALUES two comma five. That's the one that should not be allowed. So let's go ahead and put our foreign key constraint in, and so we're going to do this in our lend table and we're going to come down here after the column, so I'll put in another comma, and I'm going to say.
Foreign key C-I-D, that's our client ID, references client ID, like that. And we'll just do another one and I'm going to copy and paste so that I don't have spelling problems. And this will be book ID, and it references book, ID. So the way this works, we have the foreign key keyword, and in parentheses is the column in the table that I'm using this in.
So c_ID, and what it references in the foreign table, foreign key, it's the foreign table, so client table because it's not this table. Is considered foreign. And, so the foreign table is the client table and its column is id that is being referenced here. So this foreign key for c_id REFERENCES the client table id and likewise for the book id and the book table. And I do not want that last comma there. That will give me a syntax error. And I'm going to press Go.
And now, we get integrity violation, cannot add or update a child row, a foreign key constraint fails. And it tells us which one here it is, lend b foreign key two. It's trying to tell us. It's a little bit cryptic here but it is the book id. That's not working. And of course, we know that, because if we come down here to our insert statement, we know that it's the book ID that's referencing this key 5 that doesn't exist. So now, when we look at our joined query, we have Freddy, Freddy, and Harry have all borrowed books.
And here, we actually don't have that fourth row with Karen borrowing a book that doesn't exist. And so this is exactly what we want to have happen. We want to have that insert to be not allowed because it's referring to a row that doesn't exist. So, that's Foreign Keys and that's how they work. It's a little bit. Complicated to demonstrate it because I need to have all of the structure in order to do that. I want to show you one other thing here. We're going to go ahead and take that one out so it doesn't generate that error anymore when I press go here so we don't get any errors.
But I do want to, after all of these rows are inserted here. Then we do the SELECT FROM lend. I want to come in here and try to delete a row that's being referenced, right? And so in the book table, id 2 is being referenced. That's because Harry has borrowed a book, Rendezvous with Rama, which is number 2. So, I want to come in here and I want to try and delete that row in the book table after its been referenced and you'll see that this is also disallowed.
So if I say DELETE FROM book WHERE id equals 2. And you'll see that that's not allowed. I get an integrity, violation cannot delete or update a parent row. Foreign key constraint fails. And that should be disallowed, because that would then make this table again, out of integrity. Because this row would now, again refer to. A book that doesn't exist. So this is important also when we try to drop these tables. Now that have these foreign key constraints in place.
When I try to drop these tables, I have to do them in a particular order. Actually I have them up here already. If I try to drop the client or book table before I drop the lend table. Sometimes, actually it doesn't happen all the time. But sometimes it'll give me that foreign key constraint. And it should. It should not allow that. So I have to drop the lend table first, then the client table, and then the book table. And that works and now all those tables are gone. And our database is back to its original state for the rest of the course.
So the foreign key constraint is an important feature for working with relational tables in MySQL. Keep in mind that most of the major databases have a foreign key constraint but the syntax differs significantly among the different database systems.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 101476 Viewers
61 Video lessons · 88227 Viewers
71 Video lessons · 72109 Viewers
56 Video lessons · 103869 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Your file was successfully uploaded.