SQL PRIMARY KEY Constraint

-- create Colleges table with primary key college_id
CREATE TABLE Colleges (
  college_id INT,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50),
  CONSTRAINT CollegePK PRIMARY KEY (college_id)
);

SQL PRIMARY KEY Syntax

The syntax of the SQL PRIMARY KEY constraint is:

CREATE TABLE table_name (
  column1 data_type,
  ...,
  [CONSTRAINT constraint_name] PRIMARY KEY (column1)
);

Here,

• table_name is the name of the table to be created
• column1 is the name of the column where the PRIMARY KEY constraint is to be defined
• constraint_name is the arbitrary name given to the constraint
• [...] signifies that the code inside is optional.

Note: Although naming a constraint using [CONSTRAINT constraint_name] is optional, doing so makes it easier to make changes to and delete the constraint.

Primary Key Error

In SQL, we will get an error If we try to insert NULL or duplicate values in the primary key column.

NOT NULL Constraint Error

We get this error when we supply the primary key with a NULL value. This is because primary keys need to obey the NOT NULL constraint. For example,

-- NOT NULL Constraint Error
-- the value of primary key (college_id) is NULL
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES ("ARD12", "Star Public School");

Here, the SQL command gives us an error because we have supplied a NULL value to the primary key college_id in the Colleges table.

Fix the NOT NULL Constraint Error

-- Insertion Success
-- the value of primary key (college_id) is 1
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "Star Public School");

Here, the SQL command runs without errors because we have supplied the value 1 to the primary key i.e. college_id.

UNIQUE Constraint Error

We get this error when we supply duplicate values to the primary key, which violates its UNIQUE constraint. For example,

INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "Star Public School");

-- UNIQUE Constraint Error
-- the value of college_id is not unique
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "Star Public School");

Here, the SQL command gives us an error because we have inserted the duplicate value 1 into the primary key college_id.

Fix the UNIQUE Constraint Error

INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "Star Public School");

-- Insertion Success
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (2, "ARD12", "Star Public School");

Here, the SQL command runs without errors because we have supplied unique values 1 and 2 to college_id.

Note: There can only be one primary key in a table. However, that single primary key can contain multiple columns.

Primary Key With Multiple Columns

A primary key may also be made up of multiple columns. For example,

-- add the PRIMARY KEY constraint to multiple columns
CREATE TABLE Colleges (
  college_id INT,
  college_code VARCHAR(20),
  college_name VARCHAR(50),
  CONSTRAINT CollegePK PRIMARY KEY (college_id, college_code)
);

Here, the PRIMARY KEY constraint named CollegePK is made up of the college_id and college_code columns.

This means that the combination of college_id and college_code must be unique and these two columns cannot contain NULL values.

Primary Key Constraint With Alter Table

We can also add the PRIMARY KEY constraint to a column in an existing table using the ALTER TABLE command. For example,

For a Single Column

ALTER TABLE Colleges
ADD PRIMARY KEY (college_id);

Here, the SQL command adds the PRIMARY KEY constraint to the college_id column in the existing Colleges table.

For Multiple Columns

ALTER TABLE Colleges
ADD CONSTRAINT CollegePK PRIMARY KEY (college_id, college_code);

Here, the SQL command adds the PRIMARY KEY constraint to the college_id and college_id columns in the existing Colleges table.

Note: This command is not supported by our online SQL editor as it is based on SQLite.

Auto Increment Primary Key

It is a common practice to automatically increase the value of the primary key whenever a new row is inserted. For example,

SQL Server

-- use IDENTITY(x, y) to auto increment the value
-- x -> start value, y -> steps to increase
CREATE TABLE Colleges (
  college_id INT IDENTITY(1,1),
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50),
  CONSTRAINT CollegePK PRIMARY KEY (college_id)
);

-- insert record without college_id
INSERT INTO Colleges(college_code, college_name)
VALUES ("ARD13", "Star Public School");

Oracle

-- create sequence of numbers
CREATE SEQUENCE auto_inc
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

CREATE TABLE Colleges (
  college_id INT,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50),
  CONSTRAINT CollegePK PRIMARY KEY (college_id)
);

-- create trigger before insert to
-- add auto incremented value
CREATE TRIGGER auto_inc_trigger
BEFORE INSERT ON Colleges
FOR EACH ROW
BEGIN
SELECT auto_inc.nextval INTO :new.college_id FROM dual
END;

-- insert record without college_id
INSERT INTO Colleges(college_code, college_name)
VALUES ("ARD13", "Star Public School");

MySQL

-- AUTO_INCREMENT keyword auto increments the value
CREATE TABLE Colleges (
  college_id INT AUTO_INCREMENT,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50),
  CONSTRAINT CollegePK PRIMARY KEY (college_id)
);

-- insert record without college_id
INSERT INTO Colleges(college_code, college_name)
VALUES ("ARD13", "Star Public School");

PostgreSQL

-- SERIAL keyword auto increments the value
CREATE TABLE Colleges (
  college_id INT SERIAL,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50),
  CONSTRAINT CollegePK PRIMARY KEY (college_id)
);

-- insert record without college_id
INSERT INTO Colleges(college_code, college_name)
VALUES ("ARD13", "Star Public School");

Remove Primary Key Constraint

We can remove the PRIMARY KEY constraint in a table using the DROP clause. For example,

SQL Server, Oracle

ALTER TABLE Colleges
DROP CONSTRAINT CollegePK;

MySQL

ALTER TABLE Colleges
DROP PRIMARY KEY;

Here, the SQL command removes the PRIMARY KEY constraint from the Colleges table.

Recommended Reading:

• SQL FORIEGN KEY
• SQL Constraints