SQL PRIMARY KEY Constraint

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

PRIMARY KEY: Definition and Usage

Every table should have a primary key. It ensures that each row is uniquely identifiable. A primary key can be a single column or multiple columns (a composite key). All values in the primary key column(s) must be unique and cannot be NULL (empty or undefined). A table can have only one primary key.

Adding PRIMARY KEY Constraints

You can add a primary key constraint when you create a table (using CREATE TABLE) or later, using ALTER TABLE. The syntax varies slightly depending on your database system (MySQL, SQL Server, PostgreSQL, etc.).

PRIMARY KEY Constraint on CREATE TABLE

This example shows adding a primary key during table creation. Note the slightly different syntax 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)
);
      

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;