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
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
ADD CONSTRAINT id PRIMARY KEY (ID);
- 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
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
