Primary Key in SQL: In-depth Exploration

In this article, we will delve into the concept of “Primary Key in SQL”. So, let’s get started.

What is a Primary Key in SQL ?

A primary key in SQL is a column or a set of columns in a database table that uniquely identifies each row or record in that table. It serves as a means of enforcing data integrity and ensuring that no two rows in the table have the same values for the primary key columns.

Key Characteristics of Primary Key in SQL

Here are some key characteristics and points to note about primary keys in SQL:

1. Uniqueness: Every value in a primary key column (or combination of columns, in the case of a composite primary key) must be unique across all rows in the table. This uniqueness constraint ensures that each row can be uniquely identified.
2. Null Values: Primary key columns typically cannot contain NULL values. Each row must have a non-null value in its primary key column(s). This further ensures that every row is uniquely identifiable.
3. Indexed: Primary key columns are often automatically indexed by the DBMS to speed up the retrieval of rows based on their primary key values. This index provides efficient lookup capabilities.
4. One Primary Key per Table: A table can have only one primary key. However, a primary key can consist of multiple columns, creating a composite primary key.
5. Referential Integrity: Primary keys are commonly used in defining relationships between tables. They serve as the basis for foreign keys in related tables, enforcing referential integrity.
6. Data Integrity: Primary keys play a vital role in maintaining data integrity by ensuring the uniqueness and integrity of the data in the table.

How to Add Primary Key in SQL for a Table

We can add a primary key to a table using various methods. Here are several ways to add a primary key to a table:

At Table Creation: We can define the primary key constraint when creating the table. Here’s an example using standard SQL syntax:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Birthdate DATE,
    -- Other columns
);

ALTER TABLE Statement: If the table already exists, you can use the ALTER TABLE statement to add a primary key constraint. This is the most common method when modifying existing tables. Here’s an example:

-- Assuming you have an existing table named 'Employee'
ALTER TABLE Employee
ADD PRIMARY KEY (EmployeeID);

Using Constraints: We can add a primary key constraint separately from the table creation using the ALTER TABLE statement. This approach allows you to name the primary key constraint explicitly:

-- Assuming you have an existing table named 'Employee' and 'EmployeeID' is the primary key column
ALTER TABLE Employee
ADD CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeID);

Composite Primary Key in SQL

A composite primary key in SQL is a primary key that consists of two or more columns in a database table. It enforces uniqueness across combinations of values in those columns. This means that no two rows in the table can have the same combination of values in the specified columns. Composite primary keys are commonly used when a single column cannot uniquely identify a row, but a combination of columns does.

Here’s an example of creating a table with a composite primary key in SQL:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    OrderDate DATE,
    PRIMARY KEY (OrderID, ProductID)
);

In this example, we have created a table named Orders with a composite primary key consisting of two columns: OrderID and ProductID. This means that each combination of OrderID and ProductID must be unique in the table.

You can also add a composite primary key to an existing table using the ALTER TABLE statement.

ALTER TABLE Orders
ADD PRIMARY KEY (OrderID, ProductID);

You can also add a composite primary key to an existing table using the ALTER TABLE statement with the ADD CONSTRAINT clause, like this:

-- Assuming you have an existing table named 'Orders'
ALTER TABLE Orders
ADD CONSTRAINT PK_Orders PRIMARY KEY (OrderID, ProductID);

How to DROP a Primary Key in SQL ?

To drop a primary key in SQL, you can use the ALTER TABLE statement to remove the primary key constraint from a table. Here’s the basic syntax to drop a primary key:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Here’s an example:

Let’s say you have a table named employees with a primary key constraint named pk_employee_id on the employee_id column, and you want to drop this primary key:

ALTER TABLE employees
DROP CONSTRAINT pk_employee_id;

After executing this statement, the primary key constraint will be removed from the employees table.

Please note that the exact syntax and method for dropping a primary key may vary slightly depending on the database management system you’re using (e.g., MySQL, PostgreSQL, SQL Server).

Primary Key vs Unique Key

Aspect	Primary Key	Unique Key
Purpose	Uniquely identifies each row in a table and enforces data integrity.	Enforces uniqueness but not necessarily used for row identification.
Number of Keys	Only one primary key per table.	Multiple unique keys per table are allowed.
Null Values	Does not allow NULL values.	Allows one NULL value for each unique key column.
Indexing	Automatically indexed in most database systems.	Automatically indexed in most database systems.
Data Integrity	Enforces both uniqueness and data integrity.	Enforces uniqueness but may not necessarily be used for data integrity.
Example	Employee ID in an employee table.	Social Security Number (SSN) in a citizen database.
Syntax	CREATE TABLE table_name ( column1 data_type PRIMARY KEY, column2 data_type, … );	CREATE TABLE table_name ( column1 data_type UNIQUE, column2 data_type, … );

Primary Key vs Foreign Key

Aspect	Primary Key in SQL	Foreign Key in SQL
Purpose	Uniquely identifies each row in a table.	Establishes a relationship between tables based on the values in specified columns.
Syntax	CREATE TABLE table_name ( column1 data_type PRIMARY KEY, column2 data_type, … );	CREATE TABLE table_name ( column1 data_type, column2 data_type, foreign_key_column data_type, FOREIGN KEY (foreign_key_column) REFERENCES referenced_table(referenced_column) );
Placement	Exists in the table it is defined for.	Exists in a referencing table to link to a primary key in another table.
Number of Keys	Only one primary key per table.	Multiple foreign keys per table are allowed, referencing different tables.
Uniqueness	Must contain unique values; no duplicates allowed.	Values must match the values in the referenced primary key or unique key.
Null Values	Does not allow NULL values.	Allows NULL values, indicating no relationship.
Data Integrity	Enforces both uniqueness and data integrity.	Enforces referential integrity but not necessarily uniqueness.
Usage in Queries	Often used as the basis for retrieval and JOIN operations.	Used to establish relationships between tables and enforce referential integrity.

Conclusion : Primary Key in SQL

In this article, we explored the concept of “Primary Key in SQL.” We learned that a primary key is a column or set of columns in a database table that uniquely identifies each row in that table, ensuring data integrity and uniqueness. We discussed key characteristics of primary keys, such as their uniqueness, prohibition of NULL values, indexing, and role in maintaining data integrity.

Additionally, we looked at different methods to add a primary key to a table, whether during table creation, using the ALTER TABLE statement, or by adding constraints. We also examined composite primary keys, which consist of multiple columns and enforce uniqueness across combinations of values.

Furthermore, we explained how to drop a primary key from a table using the ALTER TABLE statement and demonstrated the syntax for doing so. We also compared primary keys with unique keys, highlighting their distinct purposes and characteristics.

Finally, we compared primary keys with foreign keys, emphasizing their roles in uniquely identifying rows and establishing relationships between tables. Understanding primary keys is fundamental for designing efficient and well-structured relational databases.

Related Articles : 

• Views in SQL : Comprehensive Guide
• Constraints in SQL
• Aggregate Functions in SQL