SQL keys are used to identify and manage data in a database. They help ensure that data is unique and organized, and prevent duplicates. Different types of keys serve different purposes, such as primary keys for identifying a specific row, foreign keys for linking data across tables, and unique keys for making sure data is not repeated. Overall, keys play a crucial role in keeping a database running smoothly.
In this post, we are going to cover all keys and their purpose. I promise you that after reading this post you don’t have to read another post to understand keys in SQL. So please read this post till the end.
Different Types of Keys in SQL
1. Primary key
Primary Key is a unique identifier for each record in a table. It is used to ensure that no two records in the table have the same value for the primary key.
For example, consider a table called “college” that contains information about colleges in a country. To ensure that each college has a unique identifier, we could create a primary key on a column called “college_id”:
CREATE TABLE college (
college_id INT PRIMARY KEY,
college_name VARCHAR(50),
college_address VARCHAR(100),
college_phone VARCHAR(20)
);To insert data into the “college” table, you can use the INSERT INTO statement:
INSERT INTO college (college_id, college_name, college_address, college_phone) VALUES (1, 'The University of Cambridge', 'Cambridge, England', '+44 1223 333333');
INSERT INTO college (college_id, college_name, college_address, college_phone) VALUES (2, 'Harvard Institute of Technology, 'San Francisco, USA', '+1 415 5551212');
INSERT INTO college (college_id, college_name, college_address, college_phone) VALUES (3, 'Tsinghua University', 'Beijing, China, '+86 10 62781111');In this example, the “college_id” column is defined as the primary key of the “college” table. This means that each college in the table must have a unique “college_id” value, and no two colleges can have the same “college_id” value.
2. Foreign key
A foreign key is a key that refers to the primary key of another table. It is used to establish a relationship between two tables.
For example, consider a table called “student” that contains information about students and a table called “college” that contains information about colleges. To relate the two tables, we could create a foreign key in the “student” table that references the primary key in the “college” table:
CREATE TABLE college (
college_id INT PRIMARY KEY,
college_name VARCHAR(50),
college_address VARCHAR(100),
college_phone VARCHAR(20)
);
CREATE TABLE student (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
grade INT,
college_id INT,
FOREIGN KEY (college_id) REFERENCES college(college_id)
);To insert data into the “college” table, you can use the INSERT INTO statement:
INSERT INTO college (college_id, college_name, college_address, college_phone) VALUES (1, 'University of Cambridge', 'Cambridge, England', '+44 1223 333333');
INSERT INTO college (college_id, college_name, college_address, college_phone) VALUES (2, 'Harvest Institute of Technology', 'San Francisco, USA', '+1 415 5551212');
INSERT INTO college (college_id, college_name, college_address, college_phone) VALUES (3, 'Tsinghua University', 'Beijing, China', '+86 10 62781111');To insert data into the “student” table, you can use the INSERT INTO statement:
INSERT INTO student (student_id, first_name, last_name, grade, college_id) VALUES (1, 'John', 'Doe', 3, 1);
INSERT INTO student (student_id, first_name, last_name, grade, college_id) VALUES (2, 'Jane', 'Doe', 4, 2);
INSERT INTO student (student_id, first_name, last_name, grade, college_id) VALUES (3, 'Jim', 'Smith', 2, 3);To display the data in the “college” table, you can use the SELECT statement:
In this example, the “college_id” column in the “student” table is defined as a foreign key that references the “college_id” primary key in the “college” table. This creates a relationship between the two tables, so that you can use a query to retrieve information about both a student and the college they attend.
3. Unique key A unique key is a constraint that ensures that all values in a column or a set of columns are unique and do not duplicate. Every table can have multiple unique keys.
For example, consider the “college” table. To ensure that each college has a unique name, you can add a unique key constraint to the “college_name” column:
CREATE TABLE college (
college_id INT PRIMARY KEY,
college_name VARCHAR(50) UNIQUE,
college_address VARCHAR(100),
college_phone VARCHAR(20)
);This means that if you try to insert a row with a duplicate college name, the database will return an error and the insert will fail.
INSERT INTO college (college_id, college_name, college_address, college_phone) VALUES (4, 'University of Cambridge', 'London, England', '+44 1223 444444');This will return an error because the “University of Cambridge” college name is already in the table, and the unique key constraint ensures that all college names are unique.
4. Composite key
A composite key is a combination of two or more columns that together create a unique identifier for each row in a table. It is essentially a primary key that consists of multiple columns.
For example, consider the “college” table. If you want to ensure that the combination of college name and address is unique for each college, you can create a composite key that consists of both columns:
CREATE TABLE college (
college_id INT PRIMARY KEY,
college_name VARCHAR(50),
college_address VARCHAR(100),
college_phone VARCHAR(20),
UNIQUE (college_name, college_address)
);This means that if you try to insert a row with a duplicate combination of college name and address, the database will return an error and the insert will fail.
INSERT INTO college (college_id, college_name, college_address, college_phone)
VALUES (4, 'Harvest Institute of Technology', 'San Francisco, USA', '+1 415 5551212');This will return an error because the combination of “Harvest Institute of Technology” college name and “San Francisco, USA” address is already in the table, and the composite key constraint ensures that the combination of these two columns is unique.
5. Candidate key
A candidate key is a column or set of columns in a table that can be used as a unique identifier for each row in the table. A table can have multiple candidate keys, but only one of them is chosen to be the primary key.
For example, consider the “college” table. If a college has a unique name and a unique phone number, either one of these columns could be chosen as the primary key, but only one can be used as the primary key at a time. In this case, both columns are candidate keys:
CREATE TABLE college (
college_id INT PRIMARY KEY,
college_name VARCHAR(50) UNIQUE,
college_address VARCHAR(100),
college_phone VARCHAR(20) UNIQUE
);This means that either the “college_name” column or the “college_phone” column can be used as the unique identifier for each row in the table. However, in this example, the “college_id” column is chosen as the primary key.
A candidate key is a column or set of columns in a table that can be used as a unique identifier for each row in the table. A table can have multiple candidate keys, but only one of them is chosen to be the primary key.
For example, consider the “college” table. If a college has a unique name and a unique phone number, either one of these columns could be chosen as the primary key, but only one can be used as the primary key at a time. In this case, both columns are candidate keys:
CREATE TABLE college (
college_id INT PRIMARY KEY,
college_name VARCHAR(50) UNIQUE,
college_address VARCHAR(100),
college_phone VARCHAR(20) UNIQUE
);This means that either the “college_name” column or the “college_phone” column can be used as the unique identifier for each row in the table. However, in this example, the “college_id” column is chosen as the primary key.
6. Surrogate key
A surrogate key is a synthetic key, or an artificial primary key, that is used as a unique identifier for each row in a table. Unlike a natural primary key, which is derived from the data in the table, a surrogate key is generated by the database system, such as by using a sequence or auto-increment feature.
For example, consider the “college” table. Instead of using a natural key, such as the college name or phone number, you can use a surrogate key as the primary key:
CREATE TABLE college (
college_id INT PRIMARY KEY AUTO_INCREMENT,
college_name VARCHAR(50),
college_address VARCHAR(100),
college_phone VARCHAR(20)
);This means that the “college_id” column will be automatically generated by the database system and will serve as the unique identifier for each row in the table. This has the advantage of being easy to manage, since you don’t have to manually assign unique values, and it can also simplify the process of establishing relationships between tables.
7. Super key
A super key is a set of one or more columns in a table that can be used to uniquely identify each row in the table. A super key can contain one or more candidate keys and any other columns that are not part of a candidate key.
For example, consider the “college” table. If a college has a unique name, unique phone number, and unique address, each of these columns, or any combination of them, could be used to uniquely identify a college. In this case, the set of columns (college_name, college_phone, college_address) is a super key:
CREATE TABLE college (
college_id INT PRIMARY KEY,
college_name VARCHAR(50) UNIQUE,
college_address VARCHAR(100) UNIQUE,
college_phone VARCHAR(20) UNIQUE
);This means that any combination of the columns (college_name, college_phone, college_address) can be used to uniquely identify a row in the “college” table.
8. Alternate key
An alternate key is a candidate key that is not selected to be the primary key in a table. In other words, an alternate key is a secondary unique identifier for a table that is not selected as the primary key, but can still be used to enforce data integrity and enforce relationships between tables.
For example, consider the “college” table. If a college has a unique name and a unique phone number, either one of these columns could be chosen as the primary key, but only one can be used as the primary key at a time. In this case, either the “college_name” column or the “college_phone” column can be selected as the primary key, and the other one becomes the alternate key:
CREATE TABLE college (
college_id INT PRIMARY KEY,
college_name VARCHAR(50) UNIQUE,
college_address VARCHAR(100),
college_phone VARCHAR(20) UNIQUE
);This means that if the “college_name” column is chosen as the primary key, the “college_phone” column becomes the alternate key. The alternate key can still be used to enforce relationships between tables and ensure data integrity, but it cannot be used as the primary key in the “college” table.