Hello and welcome to Part 2 of the article ‘Using Foreign Key constraints in MySQL. In this article we will look at what happens when we add, delete or update records in all the tables. So far we have three tables: employees, departments and projects.
As you may recall from part 1 of this article, the departments table had a ‘one-to-many’ relationship with the employees table. This implied that a department could have many employees working in it but an employee only worked (or belonged) to one department. While this may not always be the case in the real world, we’ll try to keep it simple for now.
A ‘one-to-many’ relationship between departments and employees
The employees table on the other hand has a ‘one-to-many’ relationship with the projects table. This means an employee can be a project manager on many projects but a project can only have at most, one manager.
A ‘one-to-many’ relationship between employees and projects
Based on these relationships above, we are left with the following questions:
- What happens in our database when we try to add a new employee record ?
- What happens in our database when we try to add a new department record ?
- What happens in our database when we try to add a new project record ?
- What happens in our database if we attempt to delete an employee record ?
- What happens in our database if we attempt to delete a department record ?
- What happens in our database if we attempt to delete a project record ?
- What happens in our database when we try to update an employee record ?
- What happens in our database when we try to update a department record ?
- What happens in our database when we try to update a project record ?
Adding a new record to the Employees table
Let us begin with No.1 – Adding a new record to the employees table. This should be pretty straight forward enough, to add a new record in the employees table we will use the INSERT query as follows:
INSERT INTO employees (fname, lname, salary, dept_id) VALUES ("Jamie", "Byrne", 26000, 2);
Entering the above in phpMyAdmin’s SQL window looks like this:
Observing our employees table you will see the new record has been added successfully as shown below:
Notice that we have had to explicitly add a value for the foreign key column ‘dept_id’ because an employee MUST be working in a department. This is a referential constraint between the employees and departments table. An employee therefore MUST belong to a department. If you click on the ‘dept_id’ foreign key column for this new record it will take you to the departments table as shown below:
As you can see, the new employee we added has a value of ‘2’ for the dept_id column. It references the row with the primary key value of 2 in its parent table, departments. You can see that by adding a record to the employees table, no other table in our database gets affected. Keep this observation in mind for later.
Adding a record to the Departments table
The SQL query to add a new record to the departments table would look like this:
INSERT INTO departments (dept_name) VALUES ("Research and Development");
By now you should know how to use the INSERT tab in phpMyAdmin’s graphical user-interface, if not revisit part 1 of this article. The above query creates a new row in the departments table as shown in the below screenshot:
(Using phpMyAdmin GUI)
(Using phpMyAdmin SQL window)
If your sql insert query was successful then you should see the new record inside your departments table:
Adding a record to the departments table has no effect on the employees table.
Adding a new record to the projects table
Our projects table has a foreign key column named ‘proj_mgr_id’ which is basically a copy of the primary key in the employees table. We could have named this foreign key emp_id to match the primary key name inside employees table but for the sake of semantics, proj_mgr_id seems more appropriate. A employee can manage many projects but a single project can only ever have one project manager. Since a project manager can only be an existing employee, lets create a new project named “Create Sales Training Manual” and insert it as a record into the projects table. This new project will have a ‘proj_mgr_id’ of ‘2’, which if you notice is also the value in the foreign key column for the first project in our table.
If you were to hover your mouse cursor over the foreign key column in projects for the first row, which has the value of ‘2’ you will see a little ‘tooltip’ or ‘bubble box’ that shows you the actual name associated with this referenced row in the parent table employees. The row turns pale blue and a you should see the associated name of that employee pop up. The below screenshot illustrates this:
We already know that an employee can manage more than one project. In our projects table The first project titled ‘Sales Presentation’ is being managed by ‘Palvi’, who has a proj_mgr_id of ‘2’. Don’t forget, the proj_mgr_id is a copy of the emp_id in the employees table. Lets make the employee with the ‘proj_mgr_id’ value ‘2’ also manage this new project we’re creating. Lets add this new record:
INSERT INTO projects (proj_name, proj_mgr_id) VALUES ("Create Sales Training Manual", 2);
Notice after using the INSERT tab in phpMyAdmin and selecting the value from the proj_mgr_id column pull-down, the below query is auto-generated:
We should now see our new entry in the projects table:
Conclusion after adding records to all three tables
After adding records in all three tables, we can conclude that we have no problems and information held in other tables remains consistent and unchanged.
Deleting a record from the employees table
In our employees table we have an employee named ‘Jamie Byrne’ who works in the Customer Service department. Lets assume Jamie has left the company and therefore no longer needs to be in our database. We can proceed to delete him from the employees table using an SQL DELETE query as follows:
DELETE FROM employees WHERE emp_id = 9;
Going back to our employees table you can see the record with emp_id value ‘9’ is no longer there:
In the above example we deleted an employee who was not managing a project, i.e this row in employees table was not being referenced by a child row in the child table projects. What if we attempt to delete a row that IS being referenced by a row in the child table ? Do you remember the foreign key constraint rule we created in the projects table ?
FOREIGN KEY (proj_mgr_id) REFERENCES employees (emp_id) ON DELETE CASCADE ON UPDATE CASCADE
The above is saying if we delete a row from the parent table, we want the same effect to happen to the row referencing it in the child table. Let us try and delete an employee who is currently managing a project. We will try and delete the record with emp_id value ‘5’ (Rebecca Hardy) from the employees table. This row is being referenced by a row in the child table projects via the foreign key ‘proj_mgr_id’. The ‘ON CASCADE’ rule means whatever we do to a linked row in the parent table, do the same to the row in the child table referencing it. Let us delete this record:
DELETE FROM employees WHERE emp_id = 5;
This is what our tables look like after performing the above delete query:
The foreign key constraint has ‘cascaded’ down into the projects table as intended. However, if an employee who is a project manager leaves a company, should that once managed project cease to exist ? We will return to this issue later.
Deleting a record from the departments table
Since every employee belongs to a department, it should be obvious what might happen when we attempt to delete a row from the departments table. The table ‘departments’ has a ‘one-to-many’ relationship with the table ’employees’. Just remember, a table that is on the ‘many’ side of the 1:M relationship will have the foreign key. Unlike the employees and projects relationship, the foreign key constraint rule in the child table (employees) is:
FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
ON DELETE NO ACTION ON UPDATE CASCADE
The only difference between this Foreign Key Constraint Rule and the one set up between the projects and employees table is that here we are using the ‘NO ACTION’ clause with ‘ON DELETE’. In the previous example the clause was ‘CASCADE’ with ‘ON DELETE’. You should know the DELETE query by now so we’ll skip it. Instead, click on the ‘Delete’ icon on the second row of departments table as shown below:
A message appears asking you to Confirm [OK] [Cancel], click on OK. When you do this, you will see the following error pop up on the screen:
The error message is generated by MySQL and it reads: #1451 – Cannot delete or update a parent row: a foreign key constraint fails (‘new_company’.’employees’, CONSTRAINT ’employees_ibfk_1′ FOREIGN KEY (‘dept_id’) REFERENCES ‘departments’ (‘dept_id’) ON DELETE NO ACTION ON UPDATE CASCADE)
The error message has a code which is #1451. It gives you enough information to understand what has happened. It says either a delete or an update of a parent row was attemped but it could not complete this action. The reason for this is because a foreign key constraint failed. It proceeds to tell you the definition of this foreign constraint and clearly we can see it fails because we are using the ‘NO ACTION’ clause on the ‘ON DELETE’ action.
Deleting records from the projects table
No record in the employees table is affected when we delete a record inside the projects table. This makes sense since a parent table is independent to what happens to a child table. A child table is the same thing as a referencing table. It is this table that contains foreign keys always.
In the third and final part of this article series, we will look at the update queries across all three tables and how they affect one another. I hope you’ve find this article informative and useful and I look forward to bringing you the third soon.
Leave a Reply