SQL PRIMARY KEY Constraint

A PRIMARY KEY constraint in SQL is used to uniquely identify each record in a table. It ensures that each value in the primary key column (or combination of columns) is unique and not null. This means that no two rows can have the same primary key value, and a primary key column cannot have null values.

Here’s a basic example to illustrate how a PRIMARY KEY constraint works.

Example

Consider a table named Employees that we want to create with the following columns:

• EmployeeID (which should be unique for each employee)
• FirstName
• LastName
• Position

We want to make sure that each employee has a unique EmployeeID.

Here's how you might create such a table with a PRIMARY KEY constraint:

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Position VARCHAR(50),
    PRIMARY KEY (EmployeeID)
);

Explanation

• EmployeeID INT NOT NULL specifies that EmployeeID is an integer and cannot be null.
• PRIMARY KEY (EmployeeID) ensures that EmployeeID must be unique across all rows in the Employees table.

Example Data Insertion

Let’s insert some data into the Employees table:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Position) VALUES (1, 'John', 'Doe', 'Manager');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position) VALUES (2, 'Jane', 'Smith', 'Developer');

Attempting to insert a duplicate EmployeeID will result in an error:

-- This will fail because EmployeeID 1 already exists
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position) VALUES (1, 'Mike', 'Johnson', 'Analyst');

Example Output

If you query the table with:

SELECT * FROM Employees;

You would get:

EmployeeID | FirstName | LastName | Position
-----------|-----------|----------|----------
1          | John      | Doe      | Manager
2          | Jane      | Smith    | Developer

The PRIMARY KEY constraint ensures that:

• Each EmployeeID is unique.
• No EmployeeID can be NULL.

This constraint helps maintain data integrity and ensures that each record can be uniquely identified by its EmployeeID.