Foreign Key Errors

1. Inserting with Foreign Key Errors

As we have seen in the prior tutorial, we have a set of tables that we must insert, update and delete from in a specific order when we have foreign keys. The updating of data can result in an error like the insert or the delete depending on the data.

When we insert data into a table, the most common issue with the foreign keys is the order in which we are inserting the data. The other issue that can arise is if we are inserting data into a table that does not have the referenced value from the foreign key in the parent table.

Let us revisit our data set with the representatives and departments again:

CREATE TABLE representative ( representative_id INT PRIMARY KEY, first_name VARCHAR (30) NOT NULL, last_name VARCHAR (30) NOT NULL );

CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR (100) NOT NULL, manager_id INT, constraint fk_manager FOREIGN KEY (manager_id) REFERENCES representative (representative_id) );

INSERT INTO representative (representative_id, first_name, last_name)
VALUES (1, 'Bob','Evans'), (2, 'Tango','Rushmore'), (3, 'Danika','Arkane'), (4, 'Mac','Anderson');

INSERT INTO department (department_id, department_name,manager_id)
VALUES (1, 'Sales', 1), (2, 'Marketing', 3), (3, 'IT', 4), (4, 'Finance', null), (5, 'Support', null);

Now, if we tried to insert into the department table with a value for the manager_id that did not exist, let us see what error we would get:

INSERT INTO department (department_id, department_name,manager_id)
VALUES (6, 'Test', 100);

This would give us this error as we attempted to pass in 100 in the manager_id but if we looked at the representative table, we only have values 1-4. Likewise, if we attempted to update a row in the department table’s manager_id to a value that didn’t exist, we should get the same error:

UPDATE department
SET manager_id = 100
WHERE department_id = 5;

We indeed do get the same error message as we are trying to update the Support department from a valid value (null in this case) to 100 which does not exist in the representative table. Both the insert and update statements would be invalid in this case.

2. Deleting with Foreign Key Errors

Errors can also occur if we delete from a parent table in which there are records that are associated with it in the child table. In the department table as an example, we have the Sales department having the manager_id set to 1 referencing Bob Evans in the representative table. If we tried to delete the record in the representative table:

DELETE FROM representative
WHERE representative_id = 1;

We will get the following error:

We would get this error as we’re trying to delete from the parent table representative when the department table still has that value as a foreign key. If we tried to update the representative_id in the representative table to another value, we will get the same error:

UPDATE representative
SET representative_id = 100
WHERE representative_id = 1;

If we needed to delete that particular representative, we would either have to delete the records that reference the representative_id in the department table or update the representative_id to another value.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.