Keys and unique values

 When information is stored in a database, we'll usually want to read or access that data, or part of it, for some particular purpose. Let's say we want to look up a customer's phone number so we can call them about their upcoming reservation. In a table of a few hundred customers, we might have customer names and their phone numbers, and it's pretty simple to ask the database for a customer's information. We could say, give me the record for Taylor Jenkins, and the database would return their row. But what happens when two people have the same name? That's a fairly common problem when dealing with names. If we were to ask the database to give us records matching a name that's shared by a few customers, we'd get back all the records that match. 

So how do we know which is the correct customer? We certainly wouldn't want to call the wrong person. Unique values allow us to unlock the power that databases provide. In a database, a unique value is a value that doesn't show up in any other row in a given column. So there's one and only one of any particular value for that particular field. Most DBMS tools will allow us to set this constraint, or limitation, on columns in our tables. So if we tried to enter a record with a value that's already in the table, the database will reject it until the information is changed to be unique. Unique values can also be used as keys. A key is a value we can use to refer to only one specific row or record. A primary key is the most important key in a table, though there can be others as well. A table doesn't require a primary key, but having one helps to access specific records easily. In many cases, there isn't a natural piece of information that can be used as a key, so we need to provide one. We can do this by adding a column to a table and setting that column to require a unique value. 

In many DBMS tools, this is done by making the value in the new column a number and telling the database to increment the number for every new number that's added. When we add a field like this, we create a synthetic key or a surrogate key. In some situations, we might not be able to modify the schema of the table, and we might need to use two or more fields in the data to act as a key. This is called a composite key. When you set out to define your tables, you'll need to think about what value will be used as a key or whether you need to add one yourself. Another term we'll see later on is foreign key, and this is what a primary key from one table is called when it's referenced in another table. That's important when we start to build relationships between tables to associate one record with others.