What is primary key in SQL

In last chapter, how to create SQL table, we learnt how to create tbl_employee table in our test_db database. We created a table with four columns: emp_name, emp_age, emp_salary and joining_date.

Now, what if two employees with the same names join on the same date? Or there may be scenarios where similar records may enter in the database’s table, then how to differentiate each row or record in the table? This is where primary key plays its role.

A primary key is a field or column in a table that cannot contain the duplicate values or it uniquely identifies each row in a table.

As SQL primary key constraint is defined in a field, it automatically defines the UNIQUE constraint on it.

Example of creating a Primary key in SQL

The following example shows how to use the primary key command to create a primary key in a table. In this example, we will extend the employee table (tbl_employee) fields. To uniquely identify each record of an employee, we will create a table with primary key at the table creation time. The field used for the primary key is emp_id. Following is the SQL command to create a table tbl_employee with five fields including emp_id as the primary key.

By running the above SQL command, a table tbl_employee with five fields and a primary key emp_id will be created.

Create primary key in an existing table example

If a table is already created without a primary key this is how you can create a primary key by using the SQL statement.

Suppose, tbl_employee is already created without a primary key:

Adding primary key to tbl_employee by using Alter statement

This is how a primary key can be created to a pre-existing tbl_employee table.

Note that, in order to make the emp_id field as a primary key, it must have been created with the NOT NULL constraint. If it was created as null, the above statement will produce an error.

The Composite primary key

If a primary key is made up of two or more fields then it is called as the Composite key. Note that, a table may contain only one primary key. However, two or more fields can be used to make a primary key.

Example of creating composite primary key

The example below extends the tbl_emp and adds another key, the registration_no field. We want to make emp_id and registration_no as the composite key. The statement to create a table with the composite primary key will be:

Also see Create table