How to Create Foreign and Primary Keys in MySQL

MySQL is an open-source relational database management system (RDBMS) widely used for storing, managing, and processing structured data. It is one of the most popular and widely adopted RDBMS in the world, especially in the field of application and web service development. Properly structured relational databases rely heavily on structural elements such as primary and foreign keys. 

In this article, we will discuss the purpose of these keys in MySQL and the process of creating and deleting them in a table. Additionally, we will provide a practical example of their use in a database for a more detailed understanding.

Primary Key and Its Purpose

A primary key in MySQL is a special type of constraint used to uniquely identify records in a table. It ensures the uniqueness of each row and forms the basis for linking different tables. Primary keys have several distinctive properties:

• Uniqueness: The values of this key must not be duplicated in the table.

• Immutability: Only one column can be designated as the primary key in a table. If multiple such fields are needed, composite keys should be used, but this article will not focus on them.

• Non-null Value: The value of a primary key cannot be null (NULL).

Here is an example of a users table in a database that contains a primary key:

user_id	username	email	birthdate	registration_date
1	example_user1	[email protected]	1990-05-15	2024-05-19 10:30:13
2	example_user2	[email protected]	1988-11-30	2024-05-19 11:45:10
3	example_user3	[email protected]	1999-10-15	2024-05-20 12:15:13
4	example_user4	[email protected]	1998-12-07	2024-05-20 14:34:56

In this example, the user_id field is the primary key that uniquely identifies each record.

Methods to Create a Primary Key

To create a primary key in MySQL during the table creation phase, the user must add the corresponding line to the command:

CREATE TABLE table_name (

...

field_name data_type PRIMARY KEY,

...

);

Here, the keyword PRIMARY KEY indicates the creation of the primary key field.

The ALTER TABLE command is used to modify the structure of existing tables and allows adding, modifying, and deleting columns, as well as setting various constraints and indexes. It is also used to set a primary key:

ALTER TABLE table_name
ADD PRIMARY KEY (field_name);

To delete this key in MySQL, use the following command:

ALTER TABLE table_name
DROP PRIMARY KEY;

Foreign Key and Its Purpose

A foreign key in MySQL is a table element used to establish a link with another table. When two tables are linked via a foreign key, the one containing the primary key is called the parent table, and the one with the foreign key is called the child table.

Unlike primary keys, foreign keys can contain null and duplicate values, and there can be more than one foreign key in a child table.

Methods to Create a Foreign Key

To create a foreign key in MySQL when forming a table, add the following entry to the command:

CREATE TABLE child_table_name (
...
child_field_name data_type,
FOREIGN KEY (child_field_name) REFERENCES parent_table_name(parent_field_name),
...
);

Alternatively, you can add a foreign key to an existing table using the command:

ALTER TABLE child_table_name
ADD FOREIGN KEY (child_field_name) REFERENCES parent_table_name(parent_field_name);

To delete a foreign key from a table, use:

ALTER TABLE table_name
DROP FOREIGN KEY field_name;

When creating a foreign key, you can add parameters to define actions for rows in the child table when a row in the parent table is updated (ON UPDATE) or deleted (ON DELETE). These parameters are added after the REFERENCES operator. Here is a list of possible parameters:

• CASCADE: If you delete (update) a row from the parent table, MySQL automatically deletes (updates) the related rows in the child table.

• SET NULL: If you delete (update) a row from the parent table, MySQL automatically sets the foreign key value in the child table to NULL.

• RESTRICT: If you delete (update) a row from the parent table, MySQL cancels the action (throws an error) if there are related rows in the child table.

• NO ACTION: The same as RESTRICT.

Example of Creating Keys in a Database

In this chapter, as an example, we will create a database on the Hostman cloud server. First, start MySQL on the server:

sudo mysql

Next, create a new database and switch to it:

create database TestDB;

use TestDB;

As an example, we will create two tables. The first will store categories of Hostman services, and the second will list services of different categories.

To create the first table with primary key, enter the following in the console:

CREATE TABLE Categories (
CategoryID INT PRIMARY KEY,
CategoryName VARCHAR(30) NOT NULL
);

Here, CategoryID is the unique identifier for the category, and CategoryName is its name. In this example, the primary key CategoryID is needed to link it with the second table.

Now, create the second table:

CREATE TABLE Services (
ServiceID INT PRIMARY KEY,
ServiceName VARCHAR(30) NOT NULL,
CategoryID INT,
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

In the created table:

• ServiceID is the primary key identifying the service;

• ServiceName is the name of the service;

• CategoryID is the foreign key linking the service to a specific category. This foreign key references the CategoryID column in the "Categories" table.

Next, insert data into both tables.

Hostman has three service categories, so the data for the first table will be:

INSERT INTO Categories (CategoryID, CategoryName)
VALUES
(1, 'Compute'),
(2, 'App Platform'),
(3, 'Services');

For the second table, we will take the services:

INSERT INTO Services (ServiceID, ServiceName, CategoryID)
VALUES
(101, 'Cloud servers', 1),
(102, 'Frontend apps', 2),
(103, 'Backend apps', 2),
(104, 'Databases', 3),
(105, 'Queue', 3);

Now the tables look like this:

And finally, let's check how our primary key works. To do this, we will compose a query using the LEFT JOIN operator to output general information about all services and their categories in the company:

SELECT Services.ServiceID, Services.ServiceName, Categories.CategoryName 
FROM Services 
LEFT JOIN Categories ON Services.CategoryID = Categories.CategoryID;

As a result, we get the following table:

As seen above, we have combined two tables where each service shows the category it belongs to instead of a number.