DB Design #3 - SQL Keys
Keys are crucial for data integrity and retrieval in relational databases.
In the previous post in my DB Design series, we discussed various types of table relationships and made changes to our database model for our scenario.
In the process, we came across a couple of terms which needs deep diving to understand them better.
Primary Key
Foreign Key
In this article, we will dive deeper into SQL keys, types of keys, and how it helps with data integrity and retrieval for our database model.
What are Keys in SQL?
In SQL, keys are fields in a table that are used to identify specific row(s) in a table and also to find or create relationship between tables.
Keys are considered useful because of the following
Creating relationships between two tables.
To keep data consistent and valid in the database.
Might help in fast retrieval of data.
Maintaining uniqueness in a table.
There are many types of keys that can be used for the above mentioned purposes. They are as follows:
Primary key
Super keys
Candidate keys
Unique key
Foreign key
Composite key
Primary Key (PK)
Fields in a column selected to identify each record uniquely in a table.
Columns marked as primary keys aren’t allowed to have null values.
It keeps unique values throughout the column.
A table is expected to have only one primary key, although there are cases where a table can derive its primary key using two columns. (Jump to Composite Keys)
Super Keys
Super keys are one or more keys in a table that can be used to uniquely identify a record in a table.
A table can have multiple sets of super keys for identifying records.
Consider the Customers table with the following columns: customer_id, name, email_id, SSN, city, year_of_birth.
Some of the Super Keys in the above table can be:
customer_id
SSN
email_id
customer_id, name
name, SSN, year_of_birth
One or more columns in the table can be used to identify unique rows of data from the table. All of the above combinations are super keys.
Candidate Keys
Candidate keys are a subset of super keys.
Candidate keys are the fields or columns in a table that have the potential to act as Primary Key. Out of these, all columns other than the Primary key are Candidate keys.
In the Customers table shown, we have the following columns that can be considered as Candidate Keys:
customerId
emailId
SSN
This is because all of these columns can be used to uniquely identify each record in the table.
Unique Key
Unique key can identify each row in a table uniquely like a Primary Key.
But, unlike a primary key, a unique key can have only a single null value.
It does not allow for duplicate values in the column.
Unique keys are a subset of candidate keys
Foreign Key (FK)
Foreign keys are the column of the table used to point to the primary key of another table.
It can accept multiple nulls and duplicate values.
Foreign keys helps us establish relationship between two tables.
Let us take the example from my previous article.
Let's consider two tables, "Customers" and "Orders." Multiple orders can be placed by a single customer, but each order can have only one customer associated with it.
The primary key of the "Customers" table becomes the foreign key in the "Orders" table, establishing a one-to-many relationship.
Composite Key
When creating a table, there can be scenarios where a single column doesn’t provide enough unique information to serve as primary key.
In this case, two columns in the table would be combined to serve as the primary key.
When the primary key is a combination of 2 or more columns, it is called a composite key or concatenated key.
Let us consider a scenario where we record the sales orders in a table that has the following columns:
customer_id — (Same customer can place multiple orders)
order_id — (Many products can be in the same order, hence duplicate order_ids will be present)
product_code — (Multiple customers can place an order for the same product)
None of these columns can be used as a Primary key to uniquely identify each record in the table.
We create a composite key in this scenario where the Primary key would be a combination of customer_id, order_id, and product_code.
Composite keys are also used in junction tables. Refer previous article here.
Hope you all got a clear understanding of the different types of keys available in SQL.
In the next article, we will mainly look at how to efficiently use these keys for data storage and retrieval.
Until next time!