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 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 number, telephone numbers (with area code), license number etc. can be the typical example of primary keys in SQL tables.
In this tutorial, you will learn how to create a primary key in SQL. I will show you creating primary keys on single and multiple columns at the time of creating table. The later part also shows adding the primary key after a table has been created by using 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) );
Note: This query is for MS SQL Server.
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 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 Vs MySQL is just using the comma after last column specification; before the PRIMARY KEY keyword.
Using two columns for a primary key example
As mentioned earlier, a table may contain one primary key that may consist of one or more columns in that table. This is called composite key.
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 show that these are the primary key.
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 your mind as I will use it in the DROP Primary key example later.
How to add a primary key in 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 primary key. The table looks like this in MS SQL database in the DESIGN view:
You can see no yellow key icon is visible, that shows 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.
Note: The same query works on MySQL database for adding the primary key in the existing table.
Similarly, you may add multi-column primary key by separating the column names by a comma. Before creating a primary key in existing tables, make sure if that table contains data then no duplicate record exists for the column that you want to make primary key.
If it contains duplicate entries then 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 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;
What is the difference between UNIQUE Key and Primary Key?
The UNIQUE key is also used to keep “unique” entries but allow NULL value as well (unlike Primary Key). Moreover, a table may have only one primary key, whereas, you may create one or more UNIQUE keys.
For example, your table may allow only unique email addresses, phone numbers etc. by making them UNIQUE Keys.
The primary key can be referred to the other tables as a foreign key.
