In SQL, the PRIMARY KEY
constraint is used to uniquely identify rows.
The PRIMARY KEY
constraint is simply a combination of NOT NULL
and UNIQUE
constraints. Meaning, the column cannot contain duplicate as well as NULL
values.
The process to create the PRIMARY KEY
constraint is different for different database systems. For example,
CREATE TABLE Colleges (
college_id INT,
college_code VARCHAR(20) NOT NULL,
college_name VARCHAR(50),
CONSTRAINT CollegePK PRIMARY KEY (college_id)
);
Here, the college_id column is the PRIMARY KEY
. This means, the values of this column must be unique as well as it cannot contain NULL
values.
Note: The above code works in all major database systems. However, depending on the database, there may be alternate syntax to create the primary key. Refer to the database documentation for more information.
Primary Key Error
If we try to install records in the above Colleges table with duplicate or null values for the college_id column, SQL will give us an error. For example,
-- NOT NULL Constraint Error
-- The value of the primary key is NULL
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES ("ARD12", "Star Public School");
-- Insertion Success
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 cannot insert null value for the college_id field in a table because of the UNIQUE
constraint.
Note: In a table, there can be only one primary key.
Primary Key With Multiple Columns
A primary key may also be made up of multiple columns. For example,
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 college_id and college_code columns.
This means, the combination of college_id and college_code must be unique as well as these two columns cannot contain NULL
values.
Now let's try to insert records in the Colleges table,
-- NOT NULL Constraint Error
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES ("ARD12", "Star Public School");
-- Insertion Success
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 (1, "ARD13", "Star Public School");
-- Insertion Success
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (2, "ARD12", "Star Public School");
-- UNIQUE Constraint Error
-- A row already has 1 as college_id and "ARD12" as college_code
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "Star Public School");
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 single column
ALTER TABLE Colleges
ADD PRIMARY KEY (college_id);
For multiple column
ALTER TABLE Colleges
ADD CONSTRAINT CollegePK PRIMARY KEY (college_id, college_code);
Here, the SQL command adds the PRIMARY KEY
constraint to the specified column(s) in the existing table.
Auto Increment Primary Key
It is common practice to automatically increase the value of the primary key when a new row is inserted. For example,
SQL Server
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 INTO Colleges(college_code, college_name)
VALUES ("ARD13", "Star Public School");
Oracle
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)
);
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (auto_inc.nextval, "ARD13", "Star Public School");
MySQL
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 INTO Colleges(college_code, college_name)
VALUES ("ARD13", "Star Public School");
PostgreSQL
CREATE TABLE Colleges (
college_id INT SERIAL,
college_code VARCHAR(20) NOT NULL,
college_name VARCHAR(50),
CONSTRAINT CollegePK PRIMARY KEY (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.