Primary Key

The SQL Primary Key

The SQL Primary Key is a column (or combination of columns) that uniquely identifies each record in a database table. The Primary Key also speeds up data access and is used to establish a relationship between tables.

Even though a table can only have one Primary Key, it can be defined on one or more fields. When a primary key is created on multiple fields of a table, it is called a Composite Key.

Let us say, you are developing an application called “Customer Management System” to handle all the customer data of a member-only resort. This data can include their personal details, assigned member IDs, other details of the membership they opted, etc. And in all the tables created within this database, the member ID is used to distinguish the customers from each other. So, this field will be the Primary Key.

Following is the diagram of a CUSTOMERS table that holds the personal details of the customers. And as we can observe, the primary key is defined on the CUST_ID column. Using this primary key, we can retrieve a unique record of any customer.

Points to Remember

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.

Creating an SQL Primary Key

While creating a table using the CREATE TABLE statement, you can add the primary key constraint on a particular column of the table just by to specifying the name of the column along with the keyword “PRIMARY KEY”.

Syntax

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

CREATETABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,.....
   columnN datatype,PRIMARYKEY(column_name));

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 constraint “PRIMARY KEY” on the column named ID.

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Output

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.03 sec)

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 −

INSERTINTO CUSTOMERS VALUES(3,'Kaushik',23,'Kota',2000.00);

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

INSERTINTO CUSTOMERS VALUES(3,'Chaitali',25,'Mumbai',6500.00);

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

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

Similarly, a primary key column cannot contain null values. Here, using the INSERT statement we are passing a NULL value to the primary key column (ID).

INSERTINTO CUSTOMERS VALUES(NULL,'Komal',22,'Hyderabad',4500.00);

This statement generates the following error −

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

Creating Primary Key on an Existing Column

We can also add the PRIMARY KEY constraint on an existing column of a table using the ALTER TABLE statement.

Syntax

Following is the syntax to create a primary constraint on existing columns of a table −

ALTERTABLE table_name ADDCONSTRAINTPRIMARYKEY(column_name);

Example

In this example, we are adding the PRIMARY KEY constraint on the NAME column of the existing CUSTOMERS table −

ALTERTABLE CUSTOMERS ADDCONSTRAINTPRIMARYKEY(NAME);

Output

Following is the output of the above statement −

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Dropping an SQL Primary Key

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

Syntax

Following is the syntax of the ALTER TABLE statement to can drop the Primary key constraints from the column of a table −

ALTERTABLE table_name DROPPRIMARYKEY;

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 −

ALTERTABLE CUSTOMERS DROPPRIMARYKEY;

Output

The above SQL query produces the following output −

Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

Verification

As we have dropped the Primary key from the column named ID, we can insert multiple records with the same ID. Following statement inserts four records with the same ID −

INSERTINTO CUSTOMERS VALUES(3,'Chaitali',25,'Mumbai',6500.00),(3,'Hardik',27,'Bhopal',8500.00),(3,'Komal',22,'Hyderabad',4500.00),(3,'Muffy',24,'Indore',10000.00);

If you verify the content of this table, you can find multiple records with same ID −

SELECT*FROM CUSTOMERS;

The table will be displayed as −

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik	23	Kota	2000.00
3	Chaitali	25	Mumbai	6500.00
3	Hardik	27	Bhopal	8500.00
3	Komal	22	Hyderabad	4500.00
3	Muffy	24	Indore	10000.00