SQL Primary Key

The role of a primary key

Every well-designed table needs a reliable way to point to one exact row, and that is the job of the primary key. A primary key combines two guarantees: the values must be unique, so no two rows share the same key, and they cannot be NULL, so every row has an identifier. Together these rules make the key a dependable handle that other tables and queries can use to reference a specific record.

• A table can have only one primary key.
• The key can be a single column or several columns working together (a composite key).
• Primary key values must be unique and are never allowed to be NULL.
• The primary key is often used as the target that foreign keys in other tables point to.

Defining a primary key at creation

CREATE TABLE Products (
    ProductID   INT NOT NULL PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price       DECIMAL(10, 2)
);

You can also write the primary key as a separate, named clause. This style is required for composite keys and makes the constraint easier to manage.

CREATE TABLE OrderItems (
    OrderID   INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity  INT NOT NULL,
    CONSTRAINT pk_order_items PRIMARY KEY (OrderID, ProductID)
);

In OrderItems neither OrderID nor ProductID is unique on its own, because one order contains many products and one product appears in many orders. The pair together, however, is unique, so it forms a composite primary key.

Adding or dropping a primary key later

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

-- Remove it again
ALTER TABLE Products
DROP CONSTRAINT pk_products;