SQL Query to Create Table With a Primary Key

A primary key is essential in SQL databases to uniquely identify each row in a table. It ensures data integrity by containing only unique and non-NULL values. A table can have only one primary key, which can consist of a single column or multiple columns, called a composite key.

In this article, we will explain the importance of primary keys, demonstrate how to create and manage them with SQL queries, and provide practical examples with outputs to enhance our understanding.

What is a Primary Key?

A primary key is a fundamental concept in database management, used to uniquely identify each record in a table. These characteristics make the primary key essential for establishing relationships between tables and maintaining the accuracy of stored data. It ensures data integrity by enforcing specific rules:

• Uniqueness: Each value in the primary key column(s) must be unique.
  
• Non-NULL: Primary key columns cannot have NULL values.
  
• Single Instance: A table can have only one primary key, though it can span multiple columns,forming what is known as a composite key columns forming

SQL Query to Create a Table with a Primary Key

To create a table with a primary key, use the PRIMARY KEY keyword in the column definition. The primary key ensures that each row in the table is uniquely identifiable. Here's an example:

Query:

CREATE TABLE Employee (  
    Emp_ID VARCHAR(20) NOT NULL,  
    Name VARCHAR(50) NOT NULL,  
    Age INT NOT NULL,  
    Phone_No VARCHAR(10) NOT NULL,  
    Address VARCHAR(100) NOT NULL,  
    PRIMARY KEY (Emp_ID)  
);  

Explanation:

• Emp_ID: Defined as the primary key to ensure unique identification of each employee.
  
• NOT NULL: Ensures that no field within the primary key column is left blank.

Verifying the Primary Key

To check if the primary key is successfully set, use the DESCRIBE or DESC command to view the table structure. This command provides details about the table's columns, including their names, data types, constraints, and whether they allow NULL values, helping users understand the table's schema.

Query:

DESCRIBE Employee;
Or 
DESC Employee;

Output

Adding a Primary Key to an Existing Table

Now, to create a PRIMARY KEY constraint on any column when the table already exists (NO EARLIER PRIMARY KEY DEFINED), use the following ALTER TABLE command in SQL.

Syntax

ALTER TABLE [Table_Name] ADD PRIMARY KEY (ID);

Example

Adding a primary key to the Phone_No column:

ALTER TABLE Employee ADD PRIMARY KEY (Phone_No);

Output

If the Employee table already has a primary key, the following error will occur:

Explanation:

This error is because; Only one primary key can exist. So, we have to first delete the initial PRIMARY KEY to create a new PRIMARY KEY.

Deleting and Reassigning a Primary Key

To assign a new primary key, the existing primary key must be dropped. This is necessary because a table can only have one primary key at a time.

Syntax

ALTER TABLE Table_Name DROP PRIMARY KEY;  

Example

Dropping the primary key on the Emp_ID column:

ALTER TABLE Employee DROP PRIMARY KEY;  

We can then add a new primary key:

ALTER TABLE Employee ADD PRIMARY KEY (Phone_No);  

Composite Primary Keys

A composite primary key is formed when two or more columns are used together to create a unique identifier for each row in a table. This is useful when no single column can uniquely identify a record, and the combination of multiple columns ensures uniqueness.

Query:

CREATE TABLE `Employee` ( `Emp_ID` VARCHAR(20) NOT NULL ,
                      `Name` VARCHAR(50) NOT NULL ,  
                      `Age` INT NOT NULL ,  
                      `Phone_No` VARCHAR(10) NOT NULL ,
                      `Address` VARCHAR(100) NOT NULL ,
                      PRIMARY KEY (`Emp_ID`,`Name`));

Output

Adding Composite Keys to an Existing Table

We can also add a composite key to an existing table using the ALTER TABLE command by specifying the columns that will form the composite key, ensuring that the combination of these columns remains unique for each record.

Query:

ALTER TABLE Employee
 ADD CONSTRAINT PK_CUSTID PRIMARY KEY (Emp_ID, NAME);
 DESC Employee;

Output

Conclusion

Primary keys are critical for maintaining data integrity and ensuring that each row in a table can be uniquely identified. Whether it's a simple primary key or a composite key, proper implementation ensures effective database management. By enforcing uniqueness and non-null constraints, primary keys prevent duplicate or missing data, which could lead to inconsistencies and errors.

Additionally, composite keys allow for more flexible and complex data relationships by combining multiple columns. Properly defining and managing primary keys not only supports efficient queries but also enhances database security and performance, making them an essential aspect of any well-designed database schema.