Data Science

A Quick Guide To SQL Keys

Primary key, Super key, Candidate key, and Foreign key

Index Of Contents
· Introduction To SQL Keys
· Different Types Of SQL Keys:
· 1. Primary Key
· 2. Candidate Key
· 3. Superset Key
· 4. Foreign Key
· Conclusion

Introduction To SQL Keys

An SQL key can be defined as an attribute (column) or a collection of attributes that help identify a specific row in a table. Moreover, it also helps understand the relationship between the multiple records (rows) across different tables. These database keys also use certain conditions to identify unique records in a table, allowing duplicate values to be removed.

The functionality of SQL keys can be summed up pretty much these two points:

1. The keys make it simple and quick to find and choose any given record in a database. It’s worth noting that a typical firm’s database will include between a thousand and a million records. To make matters worse, these tables will almost certainly comprise duplicate rows of data!
2. It also helps detect, reinforce, and make decisions based on the relationship between these entities when the user has to work with records from multiple tables.

Different Types Of SQL Keys:

1. Primary Key
2. Candidate Key
3. Super Key
4. Foreign Key
5. Alternate Key
6. Compound Key
7. Composite Key
8. Surrogate Key

Note that we’ll be concentrating on understanding the Primary Key, Super Key, Candidate Key, and Foreign Key in this blog post.

Before we discuss each one of these keys, let us create a SQL table that will help us visualize each of these keys.

-- create table
CREATE TABLE students(
  student_id INTEGER,
  name TEXT,
  address VARCHAR,
  aadhar_no VARCHAR,
  email_id VARCHAR
);
-- insert values
INSERT INTO students VALUES (1953, 'republ', 'PO BOX 1283','183476359364', 'book@xzy.ac.in');
INSERT INTO students VALUES (1974, 'nuclea', 'PO BOX 0975','874361284560','kalam@xyz.zc.in');
INSERT INTO students VALUES (2001, 'spawn', 'PO BOX 2336','745397536457','vis@xyz.zc.in');
INSERT INTO students VALUES (2011, 'cup', 'PO BOX 0091','354367761285','ind@xyz.zc.in');
INSERT INTO students VALUES (2014, 'mangal', 'PO BOX 0756','073465623847','isro@xyz.zc.in');
INSERT INTO students VALUES (2015, 'gravi', 'PO BOX 0444','563926490876','ligo@xyz.zc.in');
INSERT INTO students VALUES (2019, 'black', 'PO BOX 0864','095476293864','eht@xyz.zc.in');

1. Primary Key

A primary key is an attribute that identifies one unique instance for every given row in the given database. The following conditions must be met in order to be a primary key:

1. Every row must have a unique primary value.
2. None of the primary values can point to a null value.
3. Once set, the primary key values cannot be modified or updated

However, there can be multiple columns that satisfy these conditions. But it is up to the user to decide the best-suited primary key value for the table.

For example: In the students table; student_id has been selected as the primary key. We can also declare either of aadhar_no or email_id as a primary key since these point to unique values too!

2. Candidate Key

A candidate key is an attribute or a set of attributes that help identify unique tuples in the dataset. The following conditions must be met in order to be a candidate key:

1. Every row must have a unique candidate value.
2. Candidate keys can have one or more attributes
3. None of the candidate values can point to a null value.

Every database table will have atleast one candidate key. Nevertheless, the table can have multiple candidate keys. In other words, once a primary key is selected by the user, all the other columns that satisfy the above-stated conditions become candidate keys.

For example: In the students table; we have selected student_id to be the primary key. So now the other keys aadhar_no and email_id that satisfied the condition to be a primary key become candidate keys.

3. Superset Key

A Superset key is defined as a combination of one or more keys that uniquely identify a specific record in a table even though the records might individually have duplicate values. The following conditions must be met in order to be a superset key:

1. Every row might or might not have a unique value.
2. The primary keys and candidate keys are a subset of the superset key.

Superset keys can also contain additional keys that might not be required to identify a unique record.

For example: In the students table; the name of two students can be the same. However, when nameis coupled with aadhar_no it helps to uniquely identify a particular individual in the table. So here, the combination of name and aadhar_no is a super key. We select many other super keys combinations like name + email_id or address + student_id etc.

4. Foreign Key

A Foreign Key is used to link two different tables together. Since one of the columns is linked to another table’s primary key, it acts as a cross-reference between these tables.

For example: The school’s students avail transport facilities, however, these details cannot be stored in the same students table despite being related to one another. So a new table called transport is created.
The two tables are then linked to each other with the help of one of the table's primary key. In this case, we’ve used the primary key; bus_no to link both the tables.

Conclusion

In this post, we have looked at four of the most basic and widely used SQL keys while working with databases. Here is a quick summary of the same:
1. Primary Key: Is a column/attribute that uniquely identifies each row in the database.
2. Candidate Key: All the attributes apart from the primary key that uniquely identifies each row.
3. Superset Key: A single key or a group of keys that identify records/rows in the database
4. Foreign Key: An attribute that creates a meaningful link between any two given tables.

I hope this article has helped you gain a better grasp of this topic. Yes, happy learning…I’ll be back with a new blog post soon!