Why PRIMARY KEY?

Let us assume there was a requirement to store only unique values in the column. For example, employee id, student id, product id etc,. These values should be unique to identify the corresponding details and should not any duplicates. These are called key values.

In terms of database, these types of unique values are called as PRIMARY KEYs. Primary keys are required to fetch the data from the database table uniquely.

What is PRIMARY KEY?

The primary keys are the constraints that is defined on the columns to store or get the data uniquely from the database table.

In simple words, PRIMARY KEY is a constraint that applies on one or more columns which does not allow duplicate values or NULL values and have only unique values. In Database, one table have only one primary key. However, PRIMARY KEY may contain one column or more columns based on requirement.

CREATE TABLE table_name ( 
    column1 datatype,
    column2 datatype,
        …,      
   columnN datatype,
   [CONSTRAINT primarykey_name]  
   PRIMARY KEY (column_name)); 

• primarykey_name - Represents the user defined name of the primary key.
• column1, column2, column3 - Represents the name of the columns which include in table.
• column_name - Represents the name of the column on which we going to create primary key.
• table_name – Represents the name of the table going to create.

  ALTER TABLE table_name
ADD [CONSTRAINT primarykey_name] PRIMARY KEY (existing_column_names); 

• primarykey_name - Represents the user defined name of the primary key.
• existing_column_name - Represents the name of the existing column on which we are going to add primary key.
• table_name – Represents the name of the existing table.

   ALTER TABLE table_name DROP CONSTRAINT primarykey_name; 

• primarykey_name - Represents the user defined name of the primary key which is going to delete.
• table_name – Represents the name of the table.

Points to remember while creating Primary key -

• PRIMARY KEY does not allow null values.
• One table have only one Primary key.
• PRIMARY KEY does not allow duplicate values.
• PRIMARY KEY can create with one or more columns in table.
• PRIMARY KEY refers as clustered index by default.
• By using PRIMARY KEY, we can identify single row uniquely.

• By using PRIMARY KEY, we can DELETE or UPDATE specific records from table by identifying uniquely instead of multiple records on table.
• We can fetch data quickly by using primary key with the help of clustered index.
• PRIMARY KEY maintain relationship with another table by using foreign key.
• PRIMARY KEY does allow duplicate data.
• Table data gets sorted in corresponding to PRIMARY KEY.

Once primary key created, clustered index gets created by default. when we try to update the table, index also gets updated which makes updating slow.

When we not use primary key?

Primary key was not use in many to many relationships. let us take a supermarket table with products and customers. In this case both products and customers columns have duplicate values why because customer can buy different products and same products will take different customers. So, we can't add primary key to neither products nor customers.

Scenario – Creating PRIMARY KEY while creating a table.

Requirement – Creating emp_id as PRIMARY KEY while creating employee_details table. The query was as follows -

CREATE TABLE employee_details ( 
        emp_id INT NOT NULL,
        emp_name VARCHAR (20) NOT NULL,
        dept_id INT,
        PRIMARY KEY (emp_id)); 

By executing above query, emp_id was created as primary key on employee_details table.

Scenario – Creating PRIMARY KEY on existing table.

Requirement – Creating pk_emp_name as PRIMARY KEY on existing employee_details table which does not have a PRIMARY KEY column. The query was as follows -

  ALTER TABLE employee_details
ADD CONSTRAINT pk_emp_name
PRIMARY KEY (emp_name);       

By executing above query, pk_emp_name was created as PRIMARY KEY on employee_details table.

While creating primary key on existing table, make sure that column which is going to add as PRIMARY KEY should be declared with NOT NULL constraint.

Scenario – Creating PRIMARY KEY on existing table with a column which allows null values.

Requirement – Creating PRIMARY KEY on dept_id column which allows null values on employee_details table. The query was as follows -

 ALTER TABLE employee_details
 ADD CONSTRAINT pk_dept_id 
 PRIMARY KEY (dept_id);       

By executing above query, we got error like "can not define PRIMARY KEY constraint on nullable column in table employee_details".

Scenario – Deleting existing PRIMARY KEY.

Requirement – Deleting existing pk_emp_name PRIMARY KEY on employee_details table. The query was as follows -

ALTER TABLE employee_details
 DROP CONSTRAINT pk_emp_name; 

By executing above query, we can delete existing primary key on employee_details table.

What happened if we try to create primary key on table which already has primary key?

Scenario – Creating PRIMARY KEY on existing table which has primary key.

Requirement – Creating pk_emp_name as PRIMARY KEY on existing employee_details table which has a primary key column. The query was as follows -

ALTER TABLE employee_details
 ADD CONSTRAINT pk_emp_name 
 PRIMARY KEY (emp_name);     

By executing above query, we got error like " Table ' employee_details ' already has a primary key defined on it. "