SQL PRIMARY KEY Constraint

A PRIMARY KEY constraint in SQL uniquely identifies each record (row) in a database table. It's a fundamental concept in relational database design, ensuring data integrity and efficient data retrieval.

PRIMARY KEY Constraint: Definition and Usage

Primary keys are essential for managing data effectively. They guarantee that each row in a table is uniquely identifiable. A primary key can consist of a single column or multiple columns (a composite key). All values in the primary key column(s) must be unique and cannot be NULL. A table can have only one primary key.

Adding PRIMARY KEY Constraints

Primary key constraints can be defined during table creation (using CREATE TABLE) or later using ALTER TABLE. The precise syntax might differ slightly among different database systems (MySQL, SQL Server, PostgreSQL, etc.).

PRIMARY KEY Constraint on CREATE TABLE

This example shows how to add a primary key during table creation. (Note the differences between MySQL and other systems.)

CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  PRIMARY KEY (ID)
);
      

CREATE TABLE Persons (
  ID INT NOT NULL PRIMARY KEY,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT
);
      

Named PRIMARY KEY Constraints and Composite Keys

You can name your primary key constraint and define it across multiple columns.

CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)
);
      

Adding a PRIMARY KEY After Table Creation

You can add a primary key to an existing table using ALTER TABLE. The column(s) you specify must already be defined as NOT NULL.

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

--Named constraint and composite key:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);
      

ALTER TABLE Persons
DROP PRIMARY KEY;
      

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;