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 the specific row(s) and find or create relationships between tables.

Keys are considered useful because of the following

Creating relationships between two tables.
To keep data consistent and valid in the database.
Helps 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

NOTE: You should have MySQL relational database management system (RDBMS) installed

Primary Key

A primary key is created 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 you didn't define the primary key when the table was first created, you can define it later using the Alter table command:

ALTER TABLE Product
ADD CONSTRAINT PK_Product PRIMARY KEY (product_id);

Candidate Key

A candidate key is a key of a table that can be used as the primary key. A table can consist of multiple candidate keys; out of these, one can be selected as a primary key. Any column(s) that can be selected as a primary key, 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, and social security number(ssn) are candidate keys. We have identified the id to serve as the primary key. Should we drop/remove the id from the table, registration_number or ssn can serve as the 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

A unique key can identify each row in a table uniquely, just 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_number VARCHAR(100) NOT NULL UNIQUE,
  ssn INT 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 a primary key. In this case, two columns in the table would be combined to serve as the primary key.
When the primary key in the table consists of two or more columns, we have a composite key.
A composite key can also be called a 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 a 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 itself cannot be a primary key as the same order can contain the order of multiple products, thus the same order_id can be present multiple times.
product_code cannot be a primary key as more than one customer can place an order for the same product.

Based on this, it is safe to assume that the primary key should be derived from more than one column: Primary Key in the above table can be derived from: {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 columns that can be used to identify a record uniquely in a table.

Alternate Key

Alternate key is a candidate key, that is currently not selected as a 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 alternate keys.

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 INT NOT NULL,
  residence VARCHAR(200),
  dept_id INT FOREIGN KEY REFERENCES Department(dept_id)
)

Conclusion

Knowledge of keys in relational databases, and 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, how they are used, and how they relate to each other.

Please leave your comments and reviews, it would be helpful to me.

Connect with me on Linkedin or Twitter

Blog