SQL PRIMARY KEY Constraint

In SQL, the PRIMARY KEY constraint is used to uniquely identify rows. It is a combination of NOT NULL and UNIQUE constraints i.e. it cannot contain duplicate or NULL values.

-- 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)
);

Here, the college_id column is the PRIMARY KEY. This means that the values of this column must be unique and it cannot contain NULL values.

Note that the above code works in all major database systems. However, some databases may have a different syntax.

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", "p4n 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 1INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "p4n 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", "p4n Public School");

-- UNIQUE Constraint Error-- the value of college_id is not uniqueINSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "p4n 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", "p4n Public School");

-- Insertion SuccessINSERT INTO Colleges(college_id, college_code, college_name)
VALUES (2, "ARD12", "p4n 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.