Last time, we learned about Primary Keys and some of the goodness that they give you. If you missed that, remember to go back and read that here.
This time, though, we want to focus on the close kin of primary keys – the foreign key.
Foreign Keys are truly columns that point to the primary key of another table. Yes, you can create a foreign key pointing to the same table – but that’s… well… special. There are reasons, but far beyond the reasons I want to go into foreign keys here.
What a foreign key does for you is extremely important – Enforce Data Consistency.
The consistency is kept by making sure that if you have data in one table, you’ll have data in the other table. For example, if I have two tables set up like this:
Now, in this example, we have the dbo.Person table which has all of our persons that can create an order. One of the things we want to make sure of is that if there is an order, then there is a person associated with it. The foreign key will help that!
We set up the foreign key with a name – this will help you tremendously when you have to do any troubleshooting of errors. If you don’t have a name for it – in our example, FK_PersonOrder – then you’ll see an error with FK_Order_AA9038490AAF098098, and that’s just not helpful to me when I have to figure out what went wrong.
You’ll also need to tell SQL what columns should match. In our example, “(PersonID)” is made to match the column of the same name in dbo.Person. Now, let’s see what we can do with that!
Given the data we have inserted above, our dbo.Person shows the following:
Now, we can start to add Orders into our dbo.Orders table for these 3 wonderful folks.
Our first one goes in without any problems. For PersonID 1, we want to add OrderID 1 for a certain date.
Order 2, though, is attempting to be added for PersonID 4 – which doesn’t exist – for a date in the past. SQL doesn’t like PersonID 4 because of the foreign key. So, it gives us a lovely error message:
Ugh. This is definitely not the way to start any day. But, at least, SQL did stop Order data from being entered that doesn’t have a corresponding Person data. Yay, consistency!
Now, please don’t do what I usually do and set this up backward, ie FK_OrderPerson – making sure that no persons are in the dbo.Person table that isn’t in the dbo.Order table. Thankfully, it only takes a second to create some data to make sure everything is working as you expect.
So, with foreign keys explained, we can get back to ERDs and reverse-engineering them. Until next time!
1 comment