How to use foreign keys in SQL

The author choosesApache Software FoundationAs aWrite for DOnationsPart of the program accepts donations.

Introduction to the

When working on a large SQL project, you must maintain data accuracy and consistency across all tables with foreign keys. A foreign key is a column or set of columns in a relational database table that provides a connection between data in two tables. This is where referential integrity comes into play in this use case. For example, you could have a table employees with a column called job_title_id that points to a query table called job_titles.

Another example can be shown in an e-commerce database where you create a category_id column in a Products table linked to a parent Products_categories table.

Referential integrity ensures that all data references are valid and prevents inconsistent entries or orphan records. Referential integrity is also useful for preventing the entry of invalid data in a multi-user database environment.

In this tutorial, you will perform referential integrity with foreign keys on your database. Although this guide was tested on a MySQL database, it still works on other SQL-based databases with a few syntax changes.

The premise condition

To complete this tutorial, you need the following.

An Ubuntu 20.04 server with a non-Rootsudo user and a basic firewall. Follow the initial server setup guide for Ubuntu 20.04 to create a non-root user and enable the firewall.
A MySQL database server. Review the tutorial on how to install MySQL on an Ubuntu 20.04 server to set up and configure a database server.

Step 1 – Set up a sample database and tables

In this step, you will create a sample database and set up several tables. You will also insert some sample data that you will use to work with foreign keys throughout the guide.

First, connect to your server as a non-root user. Then, execute the following command to log in to your MySQL server. Replace example_user with the exact name of your non-root account.

sudo mysql -u example_user -p
Copy the code

When prompted, ENTER the password of your MySQL server’s non-root user account, then press ENTER or RETURN to continue. Next, issue the following SQL command to create a sample Company_DB database.

CREATE DATABASE company_db;
Copy the code

Verify the following output to ensure that the database was created without errors.

OutputQuery OK, 1 row affected (0.01sec)Copy the code

Once you have successfully created the database with no error messages in the output, apply the SQLUSE keyword and switch to the new Company_DB database.

USE company_db;
Copy the code

You should see the following confirmation that you have successfully switched to the Company_DB database.

OutputDatabase changed
Copy the code

Next, CREATE a job_titles TABLE using the CREATE TABLE command. This table can be used as a lookup table for all job titles in your database. Job_title_id is a primary key that uses the BIGINT data type to uniquely identify each job name in your database, which can hold up to 2^63-1 records. You use the AUTO_INCREMENT keyword to have MySQL automatically assign consecutive numeric values each time you insert a new job name.

In the CREATE TABLE command, you include a job_title_NAME column that stores the value of a human-readable job name. This column stores string values, up to 50 characters long. You will define this data type using the syntax VARCHAR(50).

After the CREATE TABLE command, instruct MySQL to use the InnoDB database ENGINE by including the ENGINE = InnoDB keyword. This is a transaction-ready, general-purpose storage engine that handles concurrency while ensuring high reliability and performance for database applications.

Execute the following command to create the job_titles table.

CREATE TABLE job_titles (
    job_title_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    job_title_name VARCHAR(50)
) ENGINE = InnoDB;
Copy the code

CREATE TABLE job_titles… After the statement, make sure your command has completed successfully by confirming the following output.

OutputQuery OK, 0 rows affected (0.03 sec)
Copy the code

You now have a query table for all the available positions in your sample company. Next, insert some sample jobs in the job_titles table.

INSERT INTO job_titles (job_title_name) VALUES ('BRANCH MANAGER');
INSERT INTO job_titles (job_title_name) VALUES ('CLERK');
INSERT INTO job_titles (job_title_name) VALUES ('LEVEL 1 SUPERVISOR');
Copy the code

After each command, you should get the following confirmation.

OutputQuery OK, 1 row affected (0.00 SEC)...Copy the code

Now that you have inserted the available job titles, query the job_titles table using the MySQLSELECT keyword to validate your data.

SELECT
job_title_id,
job_title_name            
FROM job_titles;
Copy the code

You should now see a list of all available positions, as shown in the figure below.

Output+--------------+--------------------+ | job_title_id | job_title_name | +--------------+--------------------+ | 1 2 | | BRANCH MANAGER | | at | | 3 | LEVEL 1 SUPERVISOR | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00) sec)Copy the code

Next, create a employees table. This table holds the records of all employees in the company. The job_title_ID column in the employees table points to the same column in the Job_titles table. You do this by publishing the statement FOREIGN KEY (job_title_id) REFERENCES Job_titles (job_title_id). For consistency, you use the BIGINT data type, which is the data type you use for the related columns.

In the following table employees, where employees_id is the PRIMARY KEY, you use the AUTO_INCREMENT keyword to generate the new EMPLOYEes_IDS when you insert the new value.

You use first_name and last_name text fields to capture the name of the employee, with a maximum length of 50 characters. This data type is also perfect for phone numbers. Therefore, the VARCHAR(50) data type should apply to first_name,last_name, and phone fields.

To speed up the retrieval of data from two related tables, the statement INDEX (job_title_id) is used to INDEX the job_title_id column. Again, be sure to include the keyword ENGINE = InnoDB to take advantage of the InnoDB storage ENGINE outlined in Step 1.

To create the employees table, run the following command.

CREATE TABLE employees (
    employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    job_title_id BIGINT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(50),
    INDEX (job_title_id),
    FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id) 
) ENGINE = InnoDB;
Copy the code

Make sure you get the following output to confirm that you have created the table.

OutputQuery OK, 0 rows affected (0.04 sec)
Copy the code

Now that you have the right database and tables set up for testing purposes, you will see what happens next when you insert data into the table.

Step 2 – Insert invalid data

In this step, you will insert some ownerless records into the Employees table. In this case, no master records are those with invalid JOB_TITle_IDS. From your job_titles table, you only have three valid job titles, as shown below.

BRANCH MANAGER
CLERK
LEVEL 1 SUPERVISOR

Now, try adding some invalid records to the Employees table by running the following INSERT statement.

INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (4, 'JOHN'.'DOE'.'11111');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (15, 'MARY'.'SMITH'.'22222');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (7, 'JANE'.'MIKE'.'33333');
Copy the code

All of the above INSERT statements should fail with the following error because 4,15, and 7 are invalid job_title_ids.

OutputERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`))
...
Copy the code

In the next step, you will enter valid data into the Employees table and check if the command succeeded.

Step 3 – Insert valid data

You have already seen how referential integrity prevents invalid data input when a table is interjoined with a foreign key. In other words, using foreign keys can keep your database in a consistent state, even if you don’t have to code that business logic in an external client.

In this step, you now insert valid data to see if the insertion succeeds. Run the following command.

INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'PETER'.'SMITH'.'55555');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (1, 'JOHN'.'DOE'.'11111');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'STEVE'.'KIM'.'66666');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (3, 'MARY'.'ROE'.'22222');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'JANE'.'MIKE'.'33333');
Copy the code

Since you inserted valid job_TITLE_IDS, the INSERT statement will now succeed. After each INSERT command, you receive the following output.

OutputQuery OK, 1 row affected (0.00 SEC)...Copy the code

Now, you’ll notice that implementing referential integrity is a useful way to validate data and prevent non-existent records from being entered when working with interrelated tables. Again, by using foreign keys, you are creating an optimized database that allows you to query interconnected data in an efficient way.

For example, to retrieve all employee records and spell out job titles, run the following JOIN statement against the employees and job_titles tables.

SELECT
employee_id,
employees.job_title_id,
job_titles.job_title_name,
first_name,
last_name,
phone      
FROM employees
LEFT JOIN job_titles
ON employees.job_title_id = job_titles.job_title_id;
Copy the code

In the output below, information for each employee is now displayed next to their associated role/position.

Output+-------------+--------------+--------------------+------------+-----------+-------+ | employee_id | job_title_id | job_title_name | first_name | last_name | phone | +-------------+--------------+--------------------+------------+-----------+-------+ | 5 | 1 | BRANCH MANAGER | JOHN | DOE | 11111 | | 4 | 2 | CLERK | PETER | SMITH | 55555 | | 6 | 2 | CLERK | STEVE | KIM | 66666 | | 8 | 2 | CLERK | JANE |  MIKE | 33333 | | 7 | 3 | LEVEL 1 SUPERVISOR | MARY | ROE | 22222 | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 5 rows in the set (0.00 SEC)Copy the code

As you can see in the output above, you have one BRANCH MANAGER, three Clerks, and one LEVEL 1 SUPERVISOR.

Foreign keys are also great when it comes to preventing the deletion of parent records referenced by foreign keys in child tables that have been linked. Here are a few real life examples where you can apply this.

In an e-commerce site, when you have a customer’s active order in the Sales table, you can prevent the customer’s details from being accidentally deleted from the Customers table.
In the library system, you can prevent the student from being deleted from the registers table when the student has a record in the ISSUed_books table.
In a bank, you can use the foreign key method to avoid deleting records from the Savings_accounts table when the customer has made some deposits/withdrawals in the savings_accounts_transactions table.

Similarly, you can try deleting data from your table. On your command line terminal, remove a location from the job_titles table.

DELETE FROM job_titles 
WHERE job_title_id = 1 ;
Copy the code

Because you have inserted a record titled BRANCH MANAGER into the Employees table, the DELETE statement will fail and display the following error.

OutputERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`))
Copy the code

Again, add a new character to the job_titles table.

INSERT INTO job_titles (job_title_name) VALUES ('CEO');
Copy the code

Once you have successfully executed this command, you should receive the following output.

OutputQuery OK, 1 row affected (0.00 SEC)...Copy the code

Again, query the job_titles table to check the job_title_id for the new job.

SELECT
job_title_id,
job_title_name            
FROM job_titles;
Copy the code

You should now see a list of all available positions, as shown in the figure below. Job_title_id for the CEO role is 4.

Output+--------------+--------------------+
| job_title_id | job_title_name     |
+--------------+--------------------+
|            1 | BRANCH MANAGER     |
|            2 | CLERK              |
|            3 | LEVEL 1 SUPERVISOR |
|            4 | CEO                |
+--------------+--------------------+
4 rows in set (0.00 sec)
Copy the code

You now have 4 rows in the table. Next, delete job_title_id for the new role, which is 4, before entering any related records into the Employees table.

DELETE FROM job_titles 
WHERE job_title_id = 4 ;
Copy the code

The DELETE statement should now succeed.

OutputQuery OK, 1 row affected (0.00 sec)
Copy the code

After completing all of the above tests without any errors, it is now clear that your foreign key is working as expected.

conclusion

In this tutorial, you have built a sample database with interrelated tables and practiced the use of referential integrity in a relational database management system. You have already seen the importance of foreign keys in validating and preventing data from being deleted, which would otherwise leave the database in an inconsistent state. Use the knowledge in this guide for your next database project to take advantage of foreign keys.

For more practice with MySQL databases, check out these tutorials.

How to perform MySQL transactions in PHP using the PDO PHP extension on Ubuntu 18.04
How to implement MySQL pagination with PHP on Ubuntu 18.04

Introduction to the

The premise condition

Step 1 – Set up a sample database and tables

Step 2 – Insert invalid data

Step 3 – Insert valid data

conclusion

Related Posts

[signal processing] based on multi-base digital amplitude modulation and demodulation (4APK) MATLAB source code

Learn the explosion of the Web from scratch

Xi Erqi “code farmer” iterative career: high income anxiety still