top of page
Writer's pictureanisa mumtaz

DATABASE FOUNDATIONS: KEYS AND UNIQUE VALUES



There are many keys that are used when working with a database. The most common ones are ,

  • Primary key

  • Synthetic key or surrogate key

  • Composite key

  • Foreign key


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? 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.

Unique

A table doesn't require a primary key, but having one helps to access specific records easily. So , in short, primary key is the most unique value/column in a table. When writing the code, we will specify the primary key.


Primary Key


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, 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. In conclusion, these keys will help a table to have necessary unique values when required.

Comments


bottom of page