SQL PRIMARY KEY Constraint

SQL PRIMARY KEY constraint uniquely identifies each record in a database table.

PRIMARY KEY in SQL is a column (or group of columns) that uniquely identifies the records in that table. A primary key must contain unique values and can not have any NULL value.

There can only be one primary key in a table, but that primary key can consist of one or more columns. When there are two or more columns in the primary key it is called a composite key.

A primary key automatically has a UNIQUE constraint defined on it, and it ensures that there are no duplicate or NULL values in that column.

SQL PRIMARY KEY Properties

1. No duplicate values are allowed, i.e. The column assigned as the primary key should have UNIQUE values only.
2. NO NULL values are present in the Primary key column. Hence there is a Mandatory value in the column having the Primary key.
3. Only one primary key per table exists although the Primary key may have multiple columns.
4. No new row can be inserted with the already existing primary key.
5. Primary keys can be classified into two categories Simple primary key that consists of one column and composite primary key that consists of Multiple column.
6. Defined in CREATE TABLE or ALTER TABLE statement.

There are two syntaxes to create/add primary key to a table:

• Using CREATE TABLE Statement
• Using ALTER TABLE Statement

SQL PRIMARY KEY with CREATE TABLE

SQL primary key syntax with CREATE TABLE statement is:

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
…,
CONSTRAINT pk_constraint_name PRIMARY KEY (column1, column2, …)
);

SQL PRIMARY KEY with ALTER TABLE

SQL primary key syntax with ALTER TABLE statement is

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, … column_n);

Let’s look at some examples of the PRIMARY KEY Constraint in SQL, and understand it’s working.

Create PRIMARY KEY in SQL Example

In this example, we will create primary key in a new table using CREATE TABLE statement.

Query

CREATE TABLE Persons (
  PersonID int NOT NULL PRIMARY KEY,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Age int
);

Verify SQL Primary key creation

To verify if the primary key has been successfully created, we will try adding duplicate values in primary key column, and SQL should return an error.

Query

INSERT INTO Persons VALUES
  (1,"Thakur", "Aditya", 22),
  (1, "Kumar", "Shubham", 21);

Output

Error: UNIQUE constraint failed: Persons.PersonID

Add PRIMARY KEY to a Table Example

In this example, we will add primary key to a already existing table using ALTER TABLE command.

Let’s consider previous table, and create it without primary key this time.

CREATE TABLE Persons ( 
  PersonID int,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),  Age int);

This query will add primary key to ‘Persons’ table

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (PersonID);

To remove a primary key from the table use the ALTER TABLE STATEMENT with DROP PRIMARY KEY clause.

Syntax

ALTER TABLE table_name
DROP PRIMARY KEY;

DROP PRIMARY KEY IN SQL Example

ALTER TABLE Persons
DROP PRIMARY KEY;

• A primary key is a column or a set of columns in a table that uniquely identifies each row.
• It ensures data integrity by preventing duplicate records and null values.
• A primary key can be defined on a single column (simple primary key) or multiple columns (composite primary key).
• Creating a primary key automatically creates a unique index on the key column(s), improving query performance.
• Establishing relationships between tables using SQL primary key and foreign key improve database design, reduce data redundancy, and improve data consistency.