What is a Primary Key in SQL Databases?

• The primary key (or keyword) is used to identify each row in a relational database table.
• It ensures that the tables do not contain duplicate records.
• The primary key consists of one or more columns that contain unique values.
• For example, in a vehicle registration system, a VIN (vehicle identification number) can be a primary key. As such, no two vehicles may have the same VIN.
• Similarly, ID card numbers, telephone numbers (with area code), license numbers, etc. can be typical examples of primary keys in SQL tables.

In this tutorial, you will learn how to create a primary key in SQL. I will show you how to create primary keys on single and multiple columns at the time of creating a table.

The later part also shows adding the primary key after a table has been created by using the ALTER TABLE statement.

The example of primary key as creating a table

If you are creating a table by using the CREATE TABLE statement then the primary key can be created by using the PRIMARY KEY keyword as shown in the example query below.

CREATE TABLE ex_employee(

emp_id INT NOT NULL,

e_name VARCHAR(100),

e_salary decimal(8,2),

e_age tinyint,

e_join_date datetime



PRIMARY KEY (emp_id)

);

The query for the same table with a primary key in MySQL database:

CREATE TABLE ex_employee(

emp_id INT NOT NULL PRIMARY KEY,

e_name VARCHAR(100),

e_salary decimal(8,2),

e_age tinyint,

e_join_date datetime

);

This SQL query should create a table ex_employee with a primary key emp_id in the MS SQL Server or MySQL database.

This is useful if your table is supposed to have a single column primary key.

The PRIMARY KEY keyword can also be used at the end of CREATE TABLE command as shown below:

CREATE TABLE ex_employee(

emp_id INT NOT NULL,

e_name VARCHAR(100),

e_salary decimal(8,2),

e_age tinyint,

e_join_date datetime,



PRIMARY KEY(emp_id)   

);

So, the difference between SQL Server, Access, Oracle, and MySQL is just using the comma after the last column specification; before the PRIMARY KEY keyword.

Using two columns for a primary key example

A table may contain one primary key that may consist of one or more columns in that table.

For creating a primary key in MS SQL Server with two columns, the sample code is:

CREATE TABLE ex_employee(



emp_id INT NOT NULL,

e_name VARCHAR(100),

e_salary decimal(8,2),

e_age tinyint,

e_join_date datetime



PRIMARY KEY (emp_id, e_name)

);

If you go to the DESIGN view of that table after executing this command, the primary key should be visible like this:

The yellow key symbol on emp_id and e_name fields shows that these are the primary keys.

For creating multiple column primary key in the MySQL database, the same query will work except you require adding a comma before the PRIMARY KEY keyword.

For example:

CREATE TABLE ex_employee(

emp_id INT NOT NULL,

e_name VARCHAR( 100 ) ,

e_salary DECIMAL( 8, 2 ) ,

e_age TINYINT,

e_join_date DATETIME,



PRIMARY KEY(emp_id,e_name)   

)

Notice the comma after e_join_date column name.

Naming the primary key in MS SQL Server and Oracle

You may use the CONSTRAINT keyword with PRIMARY KEY for naming the primary key constraint on single or multiple columns.

Naming the primary key also enables removing it by using the ALTER TABLE command (shown in the example later).

For naming the primary key in MS SQL Server/Oracle:

CREATE TABLE ex_employee(



emp_id INT NOT NULL,

e_name VARCHAR(100),

e_salary decimal(8,2),

e_age tinyint,

e_join_date datetime



CONSTRAINT PK_Employee PRIMARY KEY (emp_id)

);

Keep that PK_Employee name in mind as I will use it in the DROP Primary key example later.

How to add a primary key to an existing table?

Sometimes, you forget to add a primary key at the time of creating a table. Or, the need for a single or multi-column primary key is raised afterward.

In that case, you may add a primary key in the existing table by ALTER TABLE command.

Suppose we created the above table without a primary key. The table looks like this in MS SQL database in the DESIGN view:

You can see no yellow key icon is visible, which shows the table has no primary key.

For adding a primary key in that table on emp_id column by ALTER TABLE command:

ALTER TABLE ex_employee

ADD PRIMARY KEY (emp_id);

This should add a primary key and if you re-open the Design view, you will see the yellow key icon.

Similarly, you may add multi-column primary key by separating the column names with a comma.

If it contains duplicate entries then the above command (ALTER TABLE) will generate an error.

How to remove a primary key in SQL?

The primary key in SQL can be dropped by using the ALTER TABLE command with the DROP PRIMARY KEY keyword in MySQL and DROP CONSTRAINT keyword in SQL Server or Oracle databases.

The sample queries in these databases for dropping a primary key are shown below.

In MS SQL Server or Oracle databases:

ALTER TABLE ex_employee

DROP CONSTRAINT PK_Employee;

You can see, I used PK_Employee which is the name of the primary key as we used in the above section.

In MySQL database:

ALTER TABLE ex_employee

DROP PRIMARY KEY;