Over the last few posts, we’ve went over Primary Keys, Foreign Keys, Candidate Keys, and Alternate Keys. Most of y’all are probably thinking – that’s too many keys already! Why do we need even more keys?
Well, that’s because we sometimes need different ways to describe what we’ve got going on. Of the four different types of keys we’ve discussed so far, they are all different enough that we need to differentiate them and be able to explain what the differences are. For the rest of the keys that we’ll go through today, the same idea exists. They are close to the others but different enough that there is a need for another name for that type of key.
There is a super key. The super key is a single key or a group of multiple keys that can uniquely identify tuples in a table.
Let’s go through an example that we’ve used before when it comes to keys:
Now an example of a super key for the dbo.person table is the PersonID – hence why it is the Primary Key as well. I can identify each row from the PersonID. But, I can also identify each row by the combination of PersonID and LastName or PersonID and FirstName or even PersonID, LastName, and FirstName. These three combinations do have the ability to identify each row – mainly because of the PersonID – even if there are extraneous columns in this key.
One key thing to remember about super keys is that a candidate key will be a super key, but the opposite is not always true.
Finally, the last key that I want to discuss is what is known as a composite key. These are simply those candidate keys that require more than one field to identify each row uniquely. A great example of this is what I have seen named as a Sales table:
Notice that the SalesID doesn’t change for the first 3 rows that we’ve inserted. The CustomerID also did not change in our example. The ProductID for each row where we have a SalesID of 1 does change which makes the SalesID/ProductID combination work for the composite key of our table. This could also, in this scenario, work very well for a primary key.
And that, my friends, is all of the different types of keys that I am aware of. I’m sure there are some more keys out there that have slightly different meanings or different use cases. If you run across one, please feel free to reach out in the comments section and let me know about them.
Until next time!
1 comment