SQL - Primary Key

---

A PRIMARY KEY is a single column/field in a table that uniquely identifies each record in a database table. It is the most appropriate candidate key to be the main key of any table.

A table can have only one PRIMARY KEY, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a Composite Key.

A KEY is a field or combination of fields in a database table, that is used to retrieve and sort rows based on certain criteria. Keys are defined to speed up data access and, in many cases, to create a relationship between tables.

Let us say, you are developing an application called University Management System to handle all the data within an organization. When designing the architecture for the database table for students or faculty, where you can make the student_id or faculty_id column/field a primary key, so it prevents you from assigning the same Id to multiple students/faculties. That is the real-time usage of the Primary key.

Following is the diagram of a Customers table that will let you know what exactly the primary key is in a table −

We can make the Customer_Id column in the above Customers table a Primary Key, using this key, we can retrieve any customer unique record.

Creating a Primary Key

To add the primary key constraint on a particular column of a table, you need to specify the name of that column as PRIMARY KEY(COLUMN_NAME), and this column name will be considered as a primary of that table

Here are some key points of the PRIMARY KEY −

• It contains only a unique value.

• It can not be null.

• One table can have only one Primary Key.

• A primary key length cannot be more than 900 bytes.

Syntax

Following is the syntax to define a column of a table as a primary key −

CREATE TABLE TABLE_NAME (COLUMN_NAME NOT NULL PRIMARY KEY(COLUMN_NMAE));

Example

In the following example, we are trying to create a table with the name CUSTOMERS with various fields in an SQL database. While creating the table, we will add the contraint “PRIMARY KEY” on the column named ID.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Output

Following is the output of the above SQL statement −

(0 rows affected)

Verification

As we know primary key value must be unique, so you can not insert the record with the same ID. Here, we will verify the constraint created on the ID column, by inserting records with duplicate ID values.

First of all, let's insert a record into the CUSTOMERS table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 

Now, let’s insert one more record with same ID −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Chaitali', 25, 'Mumbai', 6500.00 );

As we have mentioned in the above, if any field/column is defined as Primary Key in a database table, two records can not have the same value of that column/field. Therefore, the secondf insert statement generates the following error.

Result: ERROR 1062 (23000): Duplicate entry '3' for key 'customers.PRIMARY'

Verification

Similarly, a primary key column cannot contain null values. Here, using the INSERT statement we will try passing a NULL value to the primary key column. Let’s insert a record of customer with the ID value as null.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (null, 'Komal', 22, 'MP', 4500.00 ); 

In the above created table CUSTOMERS, we made the column name ID as the primary key, so we can’t insert a record with the ID value NULL. Therefore, the INSERT statement generates the following error.

Result: ERROR 1048 (23000): Column 'ID' cannot be null

Dropping a Primary Key

If you can add a Primary Key Constraint to a column in the table, you can also drop it. This is done by using the ALTER TABLE DROP statement.

Syntax

You can drop the Primary key constraints from the column of a table using the ALTER table statement as shown below −

ALTER TABLE TABLE_NAME DROP PRIMARY KEY;

Example

Let us consider the CUSTOMERS table where we have created a primary key constraint on a column named ID. You can drop this constraint from the column ID by executing the following statement

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

The above SQL query produces the following output −

(0 rows affected)

Verification

As we have dropped the Primary key from the column ID, now we can insert multiple records with the same ID. Now, let’s insert four records with the same ID −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Chaitali', 25, 'Mumbai', 6500.00 );

If you verify the content of this table, you can find all records having the same ID −

select * from Customers;

+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  1 | Khilan   |  25 | Delhi     | 1500.00 |
|  1 | kaushik  |  23 | Kota      | 2000.00 |
|  1 | Chaitali |  25 | Mumbai    | 6500.00 |
+----+----------+-----+-----------+---------+