sql primary key, sql foreign key, sql unique key Primary, Unique and Foreign Keys in MySQL

Primary, Unique and Foreign Keys in MySQL

SQL which stands for Structured Query Language is the language that allows the developer to access and make changes to a database. SQL provides the function of adding data to the database, retrieving data from the database, updating data to the database, and removing data from the database.

SQL does not work independently on its own, it is supported or used together with RDMS (relational database management system) like MySQL which is the most common. Other RDMS used include SQL server, oracle, and Microsoft access.

MySQL being one of the relational database management systems supports and works together with SQL to access and manipulate the database

In database design, keys form part of the design and implementation phase. As a good database developer, you should understand the minimum requirements of working with a database and the most common one is that

  • There should be a way of uniquely identifying records in each table of the database
  • There should be no repetition of entries from one table to the other. This is mostly applicable while using the normal forms in databases

In this article, we shall cover what a primary key, a unique key, and a foreign key is in SQL, show their differences and similarities and how they are created in the database

  • Primary Key in MySQL

A primary key in SQL is that value or set of values that are used to uniquely identify every single record in a database. There is only one primary key in each table of the database

The primary key entry must not be null meaning it must have a value when records are being keyed in.

While creating a primary key, the PRIMARY KEY keyword is used to show the value that is being assigned the role.

Mostly, to avoid duplicates which mostly occurs while inserting the values, we do set the primary key to be an integer that is incrementing by 1 for every single record that is entered

To create a table and assign a column the primary key role in MySQL, we use the following format.

For example, creating a table for customers with id of the customer as the primary

CREATE TABLE customers (

    id int NOT NULL,

    customer_name varchar(50) NOT NULL,

    email varchar(50),

    phone_number varchar(20),

    PRIMARY KEY (id)


To use auto increment property for the primary key

CREATE TABLE customers (

    id int AUTO_INCREMENT,

    customer_name varchar(50) NOT NULL,

    email varchar(50),

    phone_number varchar(20),

    PRIMARY KEY (id)


When you execute the query, the result will be as shown below

Primary, Unique and Foreign Keys in MySQL

Add Primary key in an existing table

In a case of an existing table that did not have a primary key, you can add the primary key using this query

ALTER TABLE customers


  • Unique Key in MySQL

Unique key in SQL serves the same purpose as the primary key that is, to uniquely identify records in the database table

The only difference between primary key and the unique key is that you can have multiple unique keys in the same table while there can only exist one primary key in a single database table

The purpose of the unique key in SQL is to control duplicates while entering data

An example where a unique key is used is when you are entering people’s information and the phone number and id numbers are required. It is clear that people do not share phone numbers and id numbers

In this case, there can be a different primary key, and then for the phone numbers and id numbers you define them as unique

To create a unique key in a table column, we use a sample query below

CREATE TABLE employees (

id int(11) NOT NULL,

name varchar(50) NOT NULL,

phone varchar(20) NOT NULL,

idnumber varchar(20) NOT NULL,   

CONSTRAINT phone UNIQUE (phone),

CONSTRAINT idnumber UNIQUE (idnumber)   

) ;

The result of the above query will be as below

Primary, Unique and Foreign Keys in MySQL

To differentiate between a primary key and a unique key in the PHPMyAdmin application, the primary key is represented with a yellow key symbol while the unique key is represented by a grey key symbol

  • Foreign key in MySQL

Just as the name suggests, it is a foreign object in a table.

The foreign key is used to establish or define a relationship between two tables. Other than duplicating or repeating the entries that are found in a table in the same database, we use the foreign key to reference the entries in the new table

For example, in a case where employees’ attendance data are captured every day the employee reports to work and the employee details are already captured during registration, we use an id of the employee in the attendance data to represent the employee from the employee table

The foreign key helps to normalize the database by avoiding repetition of data

CREATE TABLE employee_data(

id int(11) NOT NULL,

employee_id int(11) NOT NULL,

time_in varchar(10) NOT NULL,

time_out varchar(10) NOT NULL,

CONSTRAINT employee_id FOREIGN KEY (employee_id)

REFERENCES employees(id);

) ;


That’s how primary key, unique key, and foreign keys are defined and used in MySQL