SQL PRIMARY KEY

What is PRIMARY KEY in SQL?

A Primary Key or Primary Key Constraint is a type of key that can uniquely identify each record (rows) of a table.

According to the data present in the database, the primary key can be composed using a single column or multiple columns. The primary key composed using multiple columns is known as a Composite Primary Key. Whenever a composite primary key is formed, the least number of columns is selected so that query results can be found faster.

Before discussing the primary key in SQL, we should first learn about the set of candidate key(s).

The candidate key is nothing but a combination of attributes of a table that can uniquely identify other attributes of the table. Candidate keys are chosen from super keys and one of these candidate keys will further become Primary Key.

Refer to the article: Candidate Keys in DBMS to learn more about Candidate Keys.

The primary key selection is made by the Data Base Administrator according to the frequency of queries.

The image below depicts the basic hierarchy of the keys in DBMS.

We can define a primary key as an attribute (or a collection of attributes) that can uniquely identify each row of a database table. The primary key is a type of key having the combination of two constraints namely NOT NULL and UNIQUE.

Since the primary keys can uniquely identify the other columns of a table, the primary keys are UNIQUE. Since the primary keys are used to fetch the records from the database, the primary keys cannot be NULL.

Now, let us discuss some properties of a Primary Key in SQL:

• A primary key may consist of a single column or multiple columns according to the data set.
• A database table can have only one primary key.
• A primary key cannot have null values. We can say that primary Keys are NOT NULL by nature.
• A primary key is UNIQUE. The primary key attribute cannot contain duplicate values (there cannot have two records having the same value of the primary key field). A primary key ensures that there are no duplicate records.

Purpose of SQL PRIMARY KEY

The selection of the right primary key in SQL is one of the most important aspects of designing the overall database. The main purpose of a primary key is to uniquely identify a record of a database table.

The primary key is used in the retrieval of query results. So, according to the most frequent type of query, a primary key is selected so that we can retrieve the data from a table in the fastest way possible.

Let us take an example to understand the concept better. Suppose we are designing the database of a college to store the details of students. We will store the name, age, CGPA, roll number, and section of students.

The student table is shown below:

Student table

Now, as we can see, there are no duplicate values, as well as there, is no null value present in the Roll_No column. But, the Name column, the CGPA column, and the Section column contain some duplicate values.

So, we should use the Roll_No column as the primary key because Roll_No has both properties of a primary key. (i.e. UNIQUE and NOT NULL).

Rules for PRIMARY KEY in SQL

Some rules must be considered before selecting the primary key of a specific database table. We will now discuss the rules of the primary keys. The rules of the primary keys are:

1. A primary key must be unique. So, the primary key column does not contain duplicate values.
2. Every relational table has one and only one primary key.
3. A primary key must be NOT NULL. So, the primary key column does not accept null values.
4. A primary key is irreducible. There must not be a subset of the primary key that can uniquely identify the attributes of a table.
5. A primary key can become a foreign key (a key that refers to the PRIMARY KEY of another table).
6. The length of a primary key cannot exceed 900 bytes in size.

SQL PRIMARY KEY for One Column

We can have primary keys for one or more columns. Let us first take an example of a database table having the primary key of one attribute or column.

Suppose we are making a database for the customers of a particular shop. We want to store the customer's name, customer phone number, and customer ID. As we can see, some customers may have the same name and some customers may have a common phone number (different customers may belong to the same family) but the customer ID is always unique (no two customers can have the same customer ID). So, we can select either customer_ID as the primary key.

We can set the customer ID on auto-increment. The AUTO_INCREMENT feature allows a number to be generated automatically whenever a new record is inserted into a table. So, whenever a new customer detail is inserted, the customer ID will get generated.

Now, let us create the table to store the data of customers and set the customer ID as the primary key of the table.

SYNTAX:

SQL PRIMARY KEY for Multiple Columns

We have seen a lot of examples where only one column or attribute was able to uniquely identify all the other attributes of a table. There are many databases where the single column is not sufficient enough in uniquely identifying the other record. Hence, we use the combination of attributes as the composite primary key.

For better understanding, let us take the example of the database of the library of the college. We want to store student names, book names, and book IDs. As we can see, two students can take the same book, so we cannot set the book ID as the primary key.

There may also be a situation where two students can have the same name, so we cannot set the student name as the primary key as well.

So, only the student name or book ID (which is unique in itself) is not sufficient enough to uniquely identify other attributes of the table. So, we must take the student's name and book ID as a composite primary key.

Now, let us create the table to store the data of the library and set the student name and book ID as the primary keys of the table.

Syntax:

SQL PRIMARY KEY on ALTER TABLE

So far, we have seen how to set the primary key at the time of table creation. But we can also set the primary key for a table after the table creation. We can use the ALTER TABLE clause to set the primary key after the creation of the table.

Let us take the same example of the citizen table that we have discussed above. We were storing the citizen name, ages, and Aadhar numbers of citizens.

Syntax:

Create a PRIMARY KEY in SQL

We can use the PRIMARY KEY clause with the CREATE TABLE statement to set an attribute as the primary key. We can also set or change the primary key of the table after its creation using ALTER TABLE clause. We will learn about the ALTER TABLE way later in this article.

Let us learn how to create a primary key in SQL by creating the database of citizens of a city. We want to store the citizen name, age, and Aadhar number of the citizens. As we can see, there may be some citizens having the same name and age but an Aadhar number is a unique number (no two citizens can have the same Aadhar number). So, we can select the Aadhar Number as the primary key.

Now, let us create the table to store the data of citizens and set the Aadhar Number as the primary key of the table.

Syntax:

DROP a PRIMARY KEY in SQL

The ALTER TABLE is also used if we want to clear or delete the primary key of a particular database table. The primary key deletion is a crucial step. Before deletion of the primary key, we should make sure that the deletion should not affect the data stored in any way.

Syntax: