sql_primarykey

SQL PRIMARY KEY Constraint

SQL PRIMARY KEY Constraint Overview

The PRIMARY KEY constraint is a fundamental concept in relational databases. It uniquely identifies each record (row) in a table.

Key characteristics of a primary key:

• Uniqueness: Primary key values must be unique across all rows in the table. No two rows can have the same primary key value.
• Non-Nullability: A primary key column cannot contain NULL values. Each row must have a value for the primary key.
• Single Primary Key per Table: A table can have only one primary key. However, this primary key can consist of one or more columns (this is called a composite primary key).

It is a best practice for most tables to have a primary key, as it ensures data integrity and provides a reliable way to reference specific rows.

Defining a PRIMARY KEY Constraint (on CREATE TABLE)

You can define a primary key when you create a table using the CREATE TABLE statement.

Method 1: Inline definition (for single-column primary keys)

The following SQL creates a PRIMARY KEY on the “EmployeeID” column when the “Employees” table is created. This syntax is widely supported.

CREATE TABLE Employees (
    EmployeeID INT NOT NULL PRIMARY KEY,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Email VARCHAR(255) UNIQUE, -- Optional: Email should also be unique
    HireDate DATE
);

Method 2: Table-level constraint definition (for single or multi-column primary keys, and allows naming the constraint)

This syntax is also widely supported and is necessary for composite primary keys or if you want to explicitly name the constraint.

CREATE TABLE OrderDetails (
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT,
    UnitPrice DECIMAL(10, 2),
    CONSTRAINT PK_OrderDetail PRIMARY KEY (OrderID, ProductID) -- Composite Primary Key
);

In the OrderDetails example, the primary key PK_OrderDetail is composed of two columns: OrderID and ProductID. The combination of these two columns must be unique for each row.

Note: The specific syntax for naming constraints (like CONSTRAINT pk_PersonID) is standard, but check your RDBMS documentation if you encounter issues.

Adding a PRIMARY KEY Constraint (on ALTER TABLE)

If a table already exists, you can add a PRIMARY KEY constraint using the ALTER TABLE statement.

For a single-column primary key:

ALTER TABLE Employees
ADD PRIMARY KEY (EmployeeID);

To name the constraint and/or define a composite primary key on an existing table:

ALTER TABLE OrderDetails
ADD CONSTRAINT PK_OrderDetail_Alt PRIMARY KEY (OrderID, ProductID);

Important: When adding a primary key to an existing table, the column(s) intended for the primary key must already be defined as NOT NULL and must contain unique values. If these conditions are not met, the ALTER TABLE statement will fail.

Dropping a PRIMARY KEY Constraint

To remove an existing PRIMARY KEY constraint, use the ALTER TABLE statement. The exact syntax can vary slightly between database systems, particularly regarding how the constraint is identified.

MySQL:

ALTER TABLE Employees
DROP PRIMARY KEY;

SQL Server / Oracle / PostgreSQL (and others that require constraint name):

First, you might need to find the name of the primary key constraint if you didn’t define it explicitly. This can often be found by querying system catalog tables (e.g., information_schema.table_constraints). Assuming the constraint is named PK_OrderDetail:

ALTER TABLE OrderDetails
DROP CONSTRAINT PK_OrderDetail;

Always consult your specific database system’s documentation for the precise syntax for dropping constraints, especially if the constraint name is system-generated.