Skip to main content
Loading Explore guides…
Database Keys · Acedra
Home
Explore
Computer Science
Database Keys
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
45 segments
Copy
Hide video
0:00
in this video we are going to talk about keys to be specific we're going to talk about the types of keys that crop up in relational databases the range of terms that apply to relational database keys might seem a little intimidating at first a particular key can be a primary key a candidate key a super key an alternate key a foreign key a surrogate key a natural key a simple key a composite key a compound key or an intelligent key and it frequently happens that a key is
0:32
of more than one of these types by the end of this video you'll understand what all this key terminology means and you'll have gained some insight into how each type of key gets used in practice this is decomplexify bringing a welcome dose of simplicity to complex topics consider a table called us states this table contains one row for each u.s state here are some sample rows now we ask the question which column of the u.s states table or which combination of columns uniquely
1:09
identifies a u.s state is it state code state name is it the combination of state code and region is it something else well we know that a state code is an official postal abbreviation for u.s state each state code officially designates exactly one u.s state in real life or to put it a different way state code is a unique identifier for a u.s state as an identifier it has one very desirable characteristic it is highly stable stability means that the values of the
1:44
identifier aren't likely to change their meanings ma refers to massachusetts and we can be confident that we're not going to wake up tomorrow to find that m ma now refers to minnesota instead but some identifiers don't possess this level of stability if we were trying to identify people using their mobile phone numbers we could easily run into problems a mobile phone number is not a stable identifier for a person because people can and quite often do change their phone numbers
2:16
with state code being a good stable unique identifier for a state we're probably going to want to designate state code as the primary key of the u.s states table like this this tells the database platform that the uniqueness of state code needs to be respected what this means in practice is that if a row currently exists in the u.s states table with state code tx and somebody tries to insert an additional row with state code tx then the insert will fail by making state code the primary key
2:48
we're signaling that state code is going to be a 100 reliable shorthand for referring to a specific state in other words we're referring to a specific row in the u.s states table it's also worth mentioning that state code can't contain a null the database platform doesn't permit you to have primary keys with nullable attributes in them this makes sense because to identify something you need an identifier value and null isn't a value now that the primary key is defined as
3:19
state code we can speak of the tx row secure in the knowledge that there won't be any ambiguity about which row we mean there will only ever be one could we have set up the primary key differently for example could we have made state name the primary key yes we could have done this while it's true that geographical entities sometimes share the same name for instance two cities might share the same name this doesn't apply to u.s states all u.s states have distinct names
3:49
and even if the u.s acquires more states in the future we should be on safe ground in assuming that no u.s state will ever share the same name as another u.s state because state name is a unique identifier just as state code is a unique identifier we say that both state code and state name are candidate keys the term candidate key reflects the fact that when we're deciding what the primary key should be these are the candidates we have to choose from are there any other candidate keys
4:21
besides these two is state capital a candidate key for the us states table we might be misled into believing that it is because no two u.s state capitals share the same name but the fact that the state capital names are all different from each other is just an accident of history states have changed their capitals many times in the past and could conceivably do so again in the latter part of the 18th century the capital of georgia was a city called augusta if georgia changes its capital to
4:52
augusta again then there will be two state capitals called augusta the capital of georgia and the capital of maine so state capital isn't a guaranteed unique shorthand for a state therefore it is not a candidate key there's a subtle but important point here about what counts as a unique identifier or key unique identification isn't just about uniquely identifying things that exist right now it's about uniquely identifying things across time even if a certain attribute or a
5:24
combination of attributes seems to act like a unique identifier with respect to things that exist right now that doesn't mean it can uniquely identify all the things that might exist in the future for some type of identifier to succeed in uniquely identifying things we need to be confident that we're never going to come across two of those things that share the same identifier value state capital doesn't pass this to summarize where we've got to so far in our u.s states example there are two
5:56
candidate keys state code and state name state code is probably a better choice of primary key than state name purely on the grounds of simplicity but in principle either will work now let's imagine a database designer who hasn't managed to pin down what the candidate keys are this designer goes overboard and sets up a primary key that consists of the combination of state code and another attribute for example the combination of state code and region inexperienced designers sometimes have
6:27
trouble determining which attributes confer uniqueness and they may end up including too many attributes in the primary key out of a fear that they might otherwise miss out an attribute that needs to be part of the key but this is a mistake and just makes things worse if the primary key is set up as state code region then the table becomes vulnerable to incorrect data like this there are now two georgia's which is not what we want the reason this situation has been able to arise is that the
7:00
primary key the designer has set up is wrong and the reason it's wrong is that it isn't irreducible we call a key irreducible if it has only the attributes it needs to have in other words only the attributes that are needed to ensure uniqueness it must not have any attributes besides those because state code region has an extra attribute that it shouldn't have namely region it isn't irreducible and so it isn't a candidate key so what can we call state code region we call it a super key
7:34
a super key is something that contains all the attributes of a candidate key plus potentially some additional attributes you don't normally need to concern yourself too much with super keys but the idea of a super key does show up in theoretically rigorous definitions of the normal forms as you'll see if you watch decomplexify's voice cod normal form video there's only one more thing to say about our u.s state's example and it relates to the fact that the us states table has
8:02
two candidate keys we've designated one of those candidate keys state code as the primary key so the database platform knows it must enforce the uniqueness of state code but what the database platform won't do unless we explicitly tell it to is enforce the uniqueness of the other candidate key state name how do we enforce the uniqueness of state name we can't make state name the primary key because we've already set up a primary key on this table and having multiple primary keys on a table is
8:33
never allowed instead we can designate state name as a so-called alternate key like this by doing this we're telling the database platform to make sure that the state names within the u.s states table are all unique our database might have other tables besides the u.s states table that also make reference to u.s states perhaps we have a voters table that records information about which state a given voter is registered to vote in ideally we want the codes in the state
9:06
of voter registration column to be drawn from the same set of codes as the ones in the u.s states table because otherwise we won't be able to relate one to the other what state is qx supposed to represent there isn't a qx in the u.s states table because there is no u.s state with a postal abbreviation qx it's safe to say qx shouldn't be allowed in the voters table so how do we disallow it the answer is we can set up what we call a foreign key constraint like this
9:38
this constraint tells the database that the state of voter registration column is a foreign key to the state code column on the u.s states table which means that every value of state of voter registration in the voters table must match a value of state code in the us states table with this foreign key in place state of voter registration can never be set to qx because there is no qx state code in the us states table foreign key constraints ensure that data in different tables hangs together
10:11
with state of voter registration set up as a foreign key to state code we can reliably join the voters table to the u.s states table using a query like this our join criterion is that the state from the voters table equals the state from the us states table and because both these states are expressed using the same convention namely as u.s state postal abbreviations like tx and ga are joined should give the desired results interestingly from the point of view of
10:42
being able to join the voters table to the us states table it really doesn't matter if the states get represented as meaningful state codes like ga or as completely arbitrary codes that we've fabricated ourselves for the purposes of this particular database we could have gone for an alternative design where each state has an arbitrarily assigned numeric state id like this in the alternative design state id is the primary key of the u.s states table and state id voter registration is a
11:14
foreign key on the voters table if we want to join the voters table to the u.s states table our query looks much as it did before except this time we join on state ids rather than state codes the output of the query is exactly the same as before state id in this example is what is known as a surrogate key a surrogate is a thing that stands in for something else so for example in our alternative design state id25 is a stand-in for ga or georgia one big difference between 25 and ga is
11:51
that lots of people in the real world are familiar with ga as a shorthand for georgia whereas nobody in the real world associates 25 with georgia 25 only means georgia in the context of our database and that's the essence of a surrogate key the surrogate keys values only have real-world meaning within the context of our database if we're using the term surrogate key to describe the state id in the alternative design we need a term to describe the state code from the original design
12:23
we refer to the state code from the original design as a natural key because its values ga tx and so on are the values that a person would naturally as it were use in real life to refer to u.s states for example when writing an address on an envelope just as we can have a natural key representing a state we can have a natural key representing a person many countries have some form of national id a national insurance number in the united kingdom for instance or a
12:52
social security number in the united states these identifiers would qualify as natural keys an email address is a natural key if your insurance company sends you a letter which has your customer number at the top of it this customer number is a natural key and it's worth noting that natural keys are also sometimes known as business keys surrogate keys are different the meaning of any particular surrogate key value isn't something that gets shared with the public
13:21
the way we decide which surrogate key values within the database represent which real world things for example which state ids represent which states is arbitrary even if every single surrogate key value in the database changed overnight which let's be clear wouldn't happen but even if it did then as long as those key values changed in a completely consistent manner everything would work exactly as it did before surrogate keys have a crucial role to play in database design
13:51
but trouble arises when we forget that each surrogate key value has to represent one very specific thing in the world the job of a surrogate key is not merely to identify a record or row if a surrogate key were just a row id then this could happen these two rows are distinct from each other because they have different state ids but what's the point of that both rows are clearly about massachusetts whereas what we really want is for all the state ids in the u.s states table to
14:22
refer to different states in this case we could resolve the problem by setting up state code as an alternate key which would ensure that we never get multiple state ids referring to the same state in other situations there might not be a suitable alternate key available but at the very least we should ensure that the process that inserts records into the table takes care to avoid inserting multiple records that refer to the same thing in the real world if you're a database designer you'll
14:52
often be faced with the decision of whether to implement a surrogate key or a natural key sometimes a robust unique identifier already exists out there in the world like a postal abbreviation for a u.s state as in our original u.s state's example this is a natural key and it's absolutely fine to use it as the primary key at other times no obvious natural key will exist if we add a table for storing recipes what natural key could we use to uniquely identify a recipe
15:24
there aren't really any good options so a surrogate key is probably a sensible choice for identifying a row in a recipe table in still other cases a real world unique identifier exists but there are limits to its scope which mean it wouldn't be a suitable primary key for example an isbn number is an internationally recognized numeric code for identifying books it is a natural key let's say we're an online bookseller and in our backend database we have a books table we might be tempted to use the
15:56
isbn as the primary key of the books table but if the books table is supposed to include ebooks as well as print books then isbn turns out not to be a suitable key because the reality is that lots of ebooks don't have an isbn number instead we are better off inventing our own book numbers which apply to ebooks as well as print books these book numbers will be contained in a column called book number which will be the primary key of the books table isbn is still a useful piece of
16:28
information so we'll still have an isbn attribute in our database it just won't be the primary key as for book number we might ask is it a surrogate key well it is a surrogate key if we ensure that the book numbers aren't publicized but if we make the book numbers visible to the public for example by displaying them on the product pages of our company's website then book number becomes a natural key one thing you may have noticed is that in most of our examples so far the
16:58
primary key has consisted of a single column this doesn't have to be the case we might have a table that gives the prices of different coffee options in a cafe clearly a coffee option in this table is uniquely identified by the combination of coffee type and cup size so we can designate the primary key as coffee type cup size we call a key composite if it consists of more than one attribute so coffee type cup size is a composite key if a key consists of just one attribute we call it a simple key
17:33
when we have a composite primary key any foreign key that references this primary key will also be composite for example if we have a table that records each person's preferred coffee option it will look like this on this table which has primary key person we define a foreign key coffee type of preferred option cup size of preferred option which references the primary key coffee type cup size on the coffee options table it's worth mentioning that some people use the term compound key
18:05
interchangeably with the term composite key other people only say compound key when they mean a particular type of composite key namely a composite key that contains at least one attribute that's a foreign key to some other table for example if we have customers and accounts and each customer can have multiple accounts and vice versa then we're probably going to have a customer table an account table and a customer account table that shows the linkages between
18:33
specific customers and specific accounts like this the primary key of the customer account table is customer id account id customer id is also a foreign key to the customer table an account id is a foreign key to the account table the primary key of the customer account table has two attributes that are both foreign keys so it is a compound key in this sense the last term we're going to look at is the term intelligent key an intelligent key is a particular type of natural key
19:08
let's suppose we're a company that makes children's toys we have a standard way of classifying the toys that we produce at a high level we have two product categories classic and modern each product category is subdivided into product subcategories the classic category has three product subcategories board games jigsaw puzzles and train sets the modern category has two product subcategories quadcopters and sentient teddy bears and finally within each product
19:39
subcategory we have individual products each represented by a product code notice the format of the product code the product code is composed of three parts the first part represents the product category classic or modern the second part represents the product subcategory like jig for jigsaw or qcp for quadcopter and the third part is a sequence number that denotes the specific product within that subcategory so for example the pine ridge electric train set has product code cls trn016
20:15
the cls part means classic and the trn part means train set very clearly the product code is a natural key but we can say more than that the product code is an intelligent key an intelligent key is a natural key made up of multiple parts stuck together with each of those parts having a meaning of its own there are some pitfalls involved in using intelligent keys because an intelligent key has meanings built into it people might start to rely on being able to extract those meanings
20:45
from the key value for example people might devise reports that show product sales by product subcategory and to accomplish this they might rely on being able to recognize the product subcategory from the middle component of the product code the part of the product code that has values like jig for jigsaw brd for board game and so on this is a very risky approach that will stop working if the company ever changes the format of its product codes no one should ever assume that the
21:13
product subcategory can be extracted from the product code instead the product subcategory should be recorded explicitly in the database in its own column and people should be encouraged to make use of that column another awkward thing about the intelligent product code is that it's not clear what's supposed to happen to it when products are re-categorized at the moment all product codes start with either cls or mod because all products are deemed to be either classic
21:42
or modern but what if the company decides to stop categorizing its products as classic and modern what if they decide to start categorizing products instead as battery operated and non-battery operated then there's going to be a conflict between the current format of the product codes and the company's new scheme of categories at the very least it's going to cause confusion and in the worst case it may require a large scale update to the existing product codes in the database
22:11
none of this means that there should be a blanket ban on intelligent keys but it does mean care needs to be taken when devising and using them intelligent keys work best when the components that make up the key value really are essential to its identity for example we might imagine an intelligent customer natural key that's made up of the customer's date of birth followed by a sequence number for example 1997.0308.0096
22:39
the customer's date of birth is never going to change so this is a stable identifier it might not be any better than a completely arbitrary customer identifier but at least it's unlikely to cause significant problems this concludes our discussion of the different types of relational database keys i hope you found this video helpful if you have any comments or questions about what you've just heard by all means post them in the comments section below and if you have any suggestions for
23:07
other complex topics that you'd like to see explained on decomplexify let me know in the comments feel free to like share and subscribe so long and thanks for watching
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat