Foreign Key Errors

1. Inserting with Foreign Key Errors

As we have seen in the prior tutorial, when we have a set of tables with foreign keys, we must insert, update and delete from in a specific order. Data changes can result in an error if not done correctly.

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

Let's 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);

Suppose we then tried to insert into the department table a value for the manager_id that did not exist:

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

We would get this error:

Query failed because of: error: insert or update on table "department" violates foreign key constraint "fk_manager"

This is because we attempted to pass in 100 in the manager_id. But if we look at the representative table, we only have values 1-4 available. Likewise, if we attempted to update a row in the department table’s manager_id to a value that didn’t exist, we would get the same error:

UPDATE department
SET manager_id = 100
WHERE department_id = 5;

We indeed do get the same error message:

Query failed because of: error: insert or update on table "department" violates foreign key constraint "fk_manager"

Again, this is because we're trying to update the department table 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 when there are records associated with it in the child table. For example, in the department table, the Sales department has the manager_id set to 1, referencing Bob Evans in the representative table. Suppose we tried to delete that record from the representative table:

DELETE FROM representative
WHERE representative_id = 1;

We will get this error:

Query failed because of: error: update or delete on table "representative" violates foreign key constraint "fk_manager" on table "department"

This is because 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:

UPDATE representative
SET representative_id = 100
WHERE representative_id = 1;
 

We would get the same error. 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.