SQL Primary Key

The Primary Key in SQL is a field (or a combination of fields) in a database table that uniquely identifies each record. It ensures that:

The primary key is essential for maintaining data integrity and ensuring that each record can be uniquely identified.

Creating a Table with a Primary Key

Let's create a simple table to demonstrate how a primary key works.

Code Example


-- Create a table with a Primary Key
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL
);

Output Table

ProductID ProductName
1 Laptop
2 Phone
3 Tablet

Explanation

- The Primary Key is assigned to the `ProductID` column, ensuring that each `ProductID` is unique and cannot be NULL.
- If you try to insert a record with a duplicate `ProductID`, SQL will throw an error.

Adding a Primary Key to an Existing Table

If a table already exists and you want to add a primary key, you can use the ALTER TABLE statement.

Code Example


-- Add a primary key to an existing table
ALTER TABLE Products
ADD CONSTRAINT PK_Product PRIMARY KEY (ProductID);

Composite Primary Key

A Composite Primary Key is a primary key that consists of more than one column. It is used when a single column cannot uniquely identify records, but a combination of columns can.

Code Example


-- Create a table with a composite primary key
CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Output Table

OrderID ProductID Quantity
1 101 2
1 102 1
2 101 3

Explanation

- In this example, the combination of `OrderID` and `ProductID` forms the composite primary key.
- This means each order can have multiple products, but each product within an order must be unique.

Primary Key Constraints and Indexes

When you define a primary key in SQL:

Advantages of Primary Keys