In this article we will look at how to use foreign key constraints in MySQL. We’ll be creating a Database with tables which are related to one another through the use of Foreign Keys. A Foreign key is nothing but a copy of a Primary Key in another table, think of it like a ‘link’ back to that table.
In order to relate the tables we need to create foreign keys in child tables that relate back to parent tables. Parent tables are the ones that are being referenced by the child tables through the use of such foreign keys. These foreign keys are nothing but copies of primary keys in other tables. This is the only way two tables can be ‘linked’ or related. Though this explanation may be the simplest one I could give you on foreign keys, I will still steer you towards understanding this better with an example when I also mention foreign key constraints. This article will assume you have MySQL installed on your computer and have access to an application to write SQL such as the mysql client or ideally phpMyAdmin. Alternatively you may also download MySQL Query Browser available at www.mysql.com.
For this article we’ll be using phpMyAdmin which provides an intuitive user interface to the MySQL Server, if you have a web hosting account you should be able to access and install it from there if you have not done so already. The other option you have is to download and install WAMP or XAMP on your computer as this will install PHP (if its not already installed ) onto your computer and allow you to use phpMyAdmin through localhost.
Creating the Company Database
So lets get started. Open up phpMyAdmin by logging in with your credentials. If you have not set up a username and/or password then most likely your username will be ‘root’ and the password can be left empty. Once you’ve logged into phpMyAdmin, you will see links at the top that read ‘Databases’ , ‘SQL’, ‘Status’ , ‘User accounts’ etc. Click on the one that reads ‘SQL’ as shown in the below screenshot. Here you will be presented a small window in which you can enter SQL (Structured Query Language) queries.
The very first thing we want to do is create our database called ‘new_company’. Enter the following SQL query to create this database:
CREATE DATABASE new_company
CHARACTER SET utf8
COLLATE utf8_general_ci
Once you have entered the query above, click on the ‘Go’ button and if you entered the code as shown you should see the database name appear in the far right hand pane.
The first line ‘CREATE DATABASE new_company’ creates a new MySQL database named new_company, the second line ‘CHARACTER SET utf-8’ specifies the encoding we want to use for our database. The encoding we choose will determine which which type of characters we can use and store inside of our database. The last line ‘COLLATE utf8_general_ci’ sets the rules to be used when comparing characters within the chosen character set.
Understanding Relationships
Before we start creating tables and establishing foreign key constraints, its worth understanding an important aspect of Databases and that is to understand what real world scenario they are modelling and consequently, how this would reflect on the relationships between the tables. In our example to keep things simple, we will be looking to emulate the following rules in our company:
1. Every employee has a unique id by which they can be identified.
2. Every employee MUST be working in one department and one only.
3. Every department has a unique id by which it can be identified.
4. Every department has a unique name.
5. Every project has a unique id by which it can be identified.
6. Every project has a unique name.
7. Every project has at least one project manager who is an employee.
8. An employee who is a project manager can manage more than one project
9. If an employee no longer works for the company, that employee can no longer be a project manager.
In Database tables, relationships can be ‘one-to-one’, ‘one-to-many’ or ‘many-to-many’. Rule number 2 states every employee must be working in one and only one department. However we are also aware that in any given department there will be many employees. This implies that the departments table has a ‘one-to-many’ relationship with the employees table. In database modelling we would describe this relationship using whats known as an Entity-Relationship-Diagram (ERD for short) as shown below:
Each record in departments table may have many linked records in employees table, but each record in employees table may have only one corresponding record in departments table.
Creating the departments table
Having visualised whats happening between these two tables, lets begin by creating the departments table first. The reason we’re creating this table first and not the employees table is because you cannot create a table with a foreign key that references a non existent table. If that sounded confusing to you reading it, don’t worry it will become clearer as we go along. Begin by clicking on the database name if you haven’t already (it appears in the left hand side pane in phpMyAdmin). Alternatively you may go to SQL window and type ‘USE’ followed by a space and then the name of the database, in our case the name of our database is ‘my_company’ although if you’ve named it differently to what I have just enter the name you gave it.
Return to the SQL window by clicking on the SQL tab and inside the window pane we will begin writing our SQL query to create our departments table as shown below:
CREATE TABLE departments (
dept_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
dept_name VARCHAR(50) NOT NULL,
PRIMARY KEY (dept_id),
CONSTRAINT no_duplicate_dept_names UNIQUE (dept_name)
) ENGINE = INNODB;
Understanding the create table query
Lets breakdown what is going on here with the CREATE TABLE departments query. We created a table named departments with two columns named ‘dept_id’ and ‘dept_name’. Every table has whats called a PRIMARY KEY Column which uniquely identifies one particular row of information or record inside that table. In this table, ‘dept_id’ is the primary key column and we have chosen its data type to be TINYINT. This data type allows you to have a value in the ranges of -128 to 127(signed) or 0 to 255(unsigned). After choosing our Data Type for this column, we then select UNSIGNED which ensures we do not want to use negative numbers, only numbers 0 and above. The NOT NULL declaration simply means that this column must be filled in and is mandatory, it cannot be null. Finally, for the ‘dept_id’ column we set it to be AUTO_INCREMENT. Setting this column to AUTO_INCREMENT means each time we create a new row of information into this table, the value will increase automatically by 1.
For the ‘dept_name’ column, we chose a data type of VARCHAR which is a variable-length from 0 to 65,535. The optional parameter inside the parenthesis indicates that it can be no longer than 50 characters in length. We call this the ‘Length’ field and we ensure that it is mandatory by declaring it to be NOT NULL. The final rule for this column is that its name must be unique. This is achieved by using the keyword CONSTRAINT followed by a name for this constraint then the keyword UNIQUE followed by the name of the column we’re applying this constraint to enclosed in parenthesis.
Creating the employees table
Now that we’ve created the departments table, we can create the employee table that ‘links’ to it. The employees table is on the ‘many’ side of the one-to-many relationship with departments. What this means is that it will have a foreign key column referencing back to a record inside the departments table. This makes sense because one employee can only be assigned to one department and cannot work in more than one. We enforce this rule by creating a foreign key that REFERENCES back to the departments table’s primary key, dept_id. To keep things simple, we have named our foreign key the same as the primary key of that table. Any table that contains a foreign key is known as a ‘child’ table, and the table it references back to is the ‘parent’ table. Knowing this, lets begin to write our SQL query to create the employees table:
CREATE TABLE employees (
emp_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
salary SMALLINT(6) NOT NULL,
dept_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (emp_id),
FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ON DELETE NO ACTION ON UPDATE cascade
) ENGINE = INNODB;
This query does the same as the one used to create the departments table except its got one extra column that has the data type of SMALLINT, is UNSIGNED and set to NOT NULL. The TINYINT data type allows values in the ranges of (-128 to 127) signed or (0 to 255) unsigned. Another column named dept_id is created to behave as the foreign key so it will need to be identical to the primary key of the table its referencing except it does not need to be auto increment, only the primary key needs to be set to auto increment. We declare the primary key of this table as emp_id and then the next line declares the foreign key constraints. Notice ON DELETE NO ACTION means if we try to delete a record from the parent table (departments) that is being linked by a foreign key from the child table (employees) it will not do so, it will take no action and in fact it will throw you an error like the one shown below:
Why the error?
DELETE NO ACTION will make sure that if there is a record in the child table that is dependent on a record inside the parent table, the delete will fail. The only time it will not fail is when there is no record in the child table that is dependent on any of the rows in the parent table, i.e no row/record in the parent table is being REFERENCED through its primary key via a foreign key inside the child table.
If we try to delete a record from the child table (employees) however, we will not get an error and the employee record will be deleted successfully without affecting its parent table. The reason this works is simple: No records (row of information) residing in the departments table are linking back to the employees table, that is, there is no foreign key constraint being enforced from that table.
Creating the projects table
The third and final table we need to create is the projects table. A Primary key column named ‘proj_id’ uniquely identifies each row/record in this table, i.e a particular project. Alongside the primary key column, we store the projects name in the ‘proj_name’ column which is of data type VARCHAR, cannot exceed 75 characters in length and is set to NOT NULL. Lastly, in this table a foreign key column named ‘proj_mgr_id’ is created which REFERENCES the employees table via that table’s ’emp_id’ primary key. The Foreign Key constraint for this projects table has a ON DELETE CASCADE and ON UPDATE CASCADE rules set up. This means if we delete a row inside the employees table that is being referenced by a row in the child table (projects) then the parent rows deletion should ‘cascade’ and continue down to the child table where it will also delete the to delete the row referencing to it with a foreign key.
CREATE TABLE projects (
proj_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
proj_name VARCHAR(75) NOT NULL,
proj_mgr_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (proj_id),
FOREIGN KEY (proj_mgr_id) REFERENCES employees (emp_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT no_duplicate_proj_names UNIQUE (proj_name)
) ENGINE = INNODB;
Interestingly, though we have added a constraint that ensures every project has a unique name, we could just as easily have not used that constraint and the projects would still all be unique despite some having similar or identical names. The reason was more of a logical one, in that, it would just make it easier to distinguish between projects based on names. If for instance we had a project with an id of 1, named ‘Sales Presentation’ and some where later down the line we had another project which was similar or identical in nature but a newer more recent version of that project, then you could enter ‘Sales Presentation1’ to distinguish between the two. If you need to take anything from having read all this, just know that a project and a project manager are two entities that are entirely dependent on each other in order to exist in our current model. Neither the project or the project manager can exist without the other in our database. Also all Projects must have a unique name.
Inserting Records into the departments table
As it stands all three tables will be empty as we have not populated them with data. Lets start adding some records into our departments table using an sql query:
INSERT INTO departments (dept_name) VALUES ("Accounts & Billing");
Once you’ve added this record into the departments table, click the ‘Browse’ tab in your phpMyAdmin to see this new row of information. You may be wondering whether there is an easier way to populate tables with rows of information in phpMyAdmin. Fortunately for you there is. Click on the tab ‘Insert’ at the top of the phpMyAdmin interface. Once you’ve done this, you will be taken to a screen where you will be shown the table column names with text boxes for you to enter in values. The below screenshot shows you what this screen looks like:
Go ahead and enter ‘Customer Support’ inside the text field for the dept_name as shown in the above screenshot. Be sure to leave the dept_id field alone as this will be generated for you automatically since we set it to AUTO_INCREMENT when creating our table. Once you’ve done this click on the ‘Browse’ tab at the top to view the new row of information in the departments table:
We can repeat this process by adding a few more dept_names until we have our departments table looking something like this:
Inserting records into the employees and projects tables
In your phpMyAdmin, click ‘SQL’ and enter the following query into the query window:
INSERT INTO employees (fname, lname, salary) VALUES ("Sandra", "Knight", 28000, 3), ("Palvi", "Sharma", 25000, 6), ("Mark", "White", 30000, 2), ("Irfan", "Malik", 38000, 7);
If you entered the above query correctly then you will have successfully populated the employees table which should now resemble something similar to this:
Lets continue to add four more employees, either by writing the INSERT query directly into the SQL query Window or by clicking on ‘Insert’ tab in the phpMyAdmin main menu. In this example we’ll show you the written query just so that you get to practice writing SQL more and more:
INSERT INTO employees (fname, lname, salary) VALUES ("Rebecca", "Hardy", 22000, 6), ("Jade", "Huangfu", 26000, 5), ("Puneet", "Johal", 35000, 7), ("Bradley", "Goodall", 30000, 2);
If all went well without a hitch you should have successfully added four more new records and your employees table should look identical to the one below:
By now you should be familiar enough with the INSERT query, if not just take a look at the values in the INSERT query below as we’ll be adding them using the INSERT tab in phpMyAdmin:
INSERT INTO projects (proj_name) VALUES ("Sales Presentation", 2), ("Oracle to SQLServer DB Migration", 7), ("Email Marketing Campaign", 5);
The screenshot below demonstrates just why phpMyAdmins interface is so useful when populating tables with foreign key constraints. If you head back into your phpMyAdmin and click on the projects table, then click on INSERT tab at the top of the interface you’ll be able to see the columns with the text fields and pull-downs. Here you will be able to populate the ‘proj_name’ and ‘proj_mgr_id’ columns of the projects table, we will be adding three rows of data into this table so at the bottom of the interface you will see a small number input with a default value of 2. Click the top arrow just once so that the value inside reads 3. Add three rows to this table by entering the values shown in the above INSERT query:
In Part 2 we will test out the relationships between all three tables by deleting and updating parent/child rows so hopefully you’ll be giving that a read too.
Cindy Albrighton says
This article helped me understand foreign keys so well! Thank you so much!