Introduction to SQL keys
In the usage of databases for storage and retrieval of data, keys are very important. Both SQL and NoSQL databases alike have the concept of keys.
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
SQL supports various types of keys:
Primary Key, Candidate Key, Unique Key, Composite Key, Super Key, Alternate Key, Foreign Key
PRIMARY KEY
A key 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.
Hint: See below — Composite key.
Primary keys can be defined using the create statement when creating a table
Adding a Primary Key To A Table
CREATE TABLE Product(
Product_ID int not null PRIMARY KEY,
Product_name varchar(200) not null
)Another way to create a table with the primary key
CREATE TABLE Product(
Product_ID int not null,
Product_name varchar(200) not null,
PRIMARY KEY (Product_ID)
)Suppose we didn’t define the primary key when the table was created, we can define it later like this:
ALTER TABLE Product
ADD CONSTRAINT PK_Product PRIMARY KEY (Product_ID);CANDIDATE KEY
Candidate key is a key of a table which can be used as the primary key. A table can consist of multiple candidate keys, and out of these, one can be selected as a primary key. Any column that can be selected as primary key and it still makes sense, that column or columns are candidate keys.
CREATE TABLE Students(
id PRIMARY KEY not null
first_name varchar(100),
last_name varchar(100),
email varchar(255) not null,
registration_number not null,
ssn integer not null,
residence varchar(200)
)Id, registration_number, ssn are candidate keys. We have identified id to serve as the primary key. Should we drop/remove id from the table, registration_number or ssn can serve as primary key. This is because each attribute is unique to each student. It wouldn’t be right to have two students that have the same registration number or social security number (ssn).
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 and it does not allow for duplicate values in the column. In the students table, registration_nos is identified as the unique key. We can’t have two students having the same registration number.
CREATE TABLE Students(
id PRIMARY KEY not null,
first_name varchar(100),
last_name varchar(100),
email varchar(100) not null,
registration_Nos varchar(100) not null Unique,
ssn integer not null,
residence varchar(200)
)Composite Key
When creating a table, situations may arise 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.
Get Ifeanyichukwu Onyechere’s stories in your inbox
Join Medium for free to get updates from this writer.
When the primary key in the table consists of two or more columns, we have a composite key. Composite key can also be called compound or concatenated key.
CREATE TABLE sales_order(
customer_id varchar(150) not null,
order_id int not null,
product_code varchar(150) not null,
primary key (customer_id, product_code)
)customer_id order_id product_code
-------- -------- ------------
C01 O001 P007
C02 O123 P007
C02 O123 P230
C01 O001 P890
None of these columns alone can play a role as primary key.
customer_id alone cannot become a key as the same customer can place multiple orders, thus the same customer can have multiple entries.
order_id alone cannot be a primary key as a same order can contain the order of multiple products, thus same order_id can be present multiple times.
product_code cannot be a primary key as more than one customer can place order for the same product.
Based on this, it is safe to assume that the primary key should be having more than one attribute: Primary Key in above table: {customer_id, product_code}
Super Key
It is a set of columns that uniquely identifies each row in a table. It is the combination of two or more column that can be used to identify a record uniquely in a table.
Alternate Key
Alternate key is a candidate key, currently not selected as primary key. It can work as a primary key, but currently isn’t the primary key. Alternate keys can also be called secondary keys.
ssn, registration_number in the above defined student table are examples of an alternate key.
Foreign Key
Foreign Key is a column in a table that is used as the Primary key in another table. It can accept multiple nulls and duplicate values. In the students table, the dept_id column identifies as the foreign key, because it is the primary key in the department table and it relates each student to a department (Each student must belong to a department).
CREATE TABLE Department (
dept_id int PRIMARY KEY,
name varchar(50) NOT NULL,
location varchar(200) NOT NULL
)CREATE TABLE Students (
id primary key not null,
first_name varchar(100),
last_name varchar(100),
email varchar(255) not null,
registration_nos not null Unique,
ssn integer not null,
residence varchar(200),
dept_id int FOREIGN KEY REFERENCES Department(dept_id)
)
Knowledge of keys in relational databases, how they are defined and used is an important concept for any SQL developer or administrator.
In this article, we have looked at the different types of keys and how the relate to each other.
I do believe you now have an understanding of the different types of keys and how they are used.
Please leave your comments and reviews, it would be helpful to me.
Go from SELECT * to interview-worthy project. Get our free 5-page guide.








