Skip to main content

Detecting drops of foreign key constraints on MySQL

Introduction​

This document describes in detail the detection of dropping foreign keys in MySQL databases, using Atlas's linting capabilities.

A foreign key constraint can be dropped from a table using the following command:

ALTER TABLE pets DROP FOREIGN KEY owner_id;

While this is sometimes desired, dropping a foreign key can have negative and sometimes unpredictable side effects which should be taken into consideration. Atlas's migrate lint command can be used to detect when a foreign key is about to be dropped. For example:

atlas migrate lint \
  --dir file://migrations \
  --dev-url docker://mysql/8/dev \
  --latest 1

If the file contains a foreign key drop, the output will be something like:

20230305125128.sql: constraint deletion detected:

    L1: Dropping foreign-key constraint "owner_id"

Impact of dropping foreign key constraints​

Losing referential integrity​

Foreign keys are the means by which a relational database maintains the Referential Integrity of the data it stores. In short, this means that a database can enforce that all references that it contains are valid.

For this reason, dropping a foreign key constraint should be done after considering the implication of no longer being able to guarantee referential integrity in the database.

Suppose our database schema contains a foreign key constraint between the pets and users tables:

Pets ERD

As long as this constraint exists, it is not possible to insert records into the pets table that refer to a non-existing record in the users table. If we try to insert such a record

INSERT INTO pets (name, owner_id) VALUES ('PetWithFakeUser', 3333);

Our statement will fail with the following error:

Cannot add or update a child row: a foreign key constraint fails (`pets`.`#sql-1_1140`, CONSTRAINT `owner_id` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`))

We can drop the foreign key between pets and users with the following command: 

ALTER TABLE pets DROP FOREIGN KEY owner_fk;

Now, inserting data into the pets table is still possible, however it is also possible to insert "invalid" data, like with the insertion above.

Losing referential integrity can result in an unexpected impact on a system. For example, a query containing a JOIN statement that was previously guaranteed to return a result, may not return one in some cases:

SELECT
    pets.name,
    users.name AS owner_name
FROM pets JOIN users ON pets.owner_id = users.id
WHERE pets.name = "spidey";

After dropping the constraint, any code that relied on the invariant that pets are always linked to an existing owner may suddenly begin to fail.

Irreversible changes​

It is also important to note that dropping a foreign key constraint is not always a reversible operation. Irreversible operations increase the risk of any change. This is because if something unexpected happens, it will not be possible to bring the system to its previous stable state.

Continuing from our example above, if after dropping the foreign key constraint between the pets and users tables, we insert pets records that refer to a non-existing user, we will not be able to re-create the foreign key until these records are altered or deleted.

Following the previous example, trying to recreate the constraint will fail:

ALTER TABLE pets ADD CONSTRAINT owner_id FOREIGN KEY (owner_id) REFERENCES users (id);
Cannot add or update a child row: a foreign key constraint fails (`pets`.`#sql-1_1140`, CONSTRAINT `owner_id` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`))

Referential actions​

Referential actions are policies that instruct the database what to do when referenced data is deleted or modified. MySQL supports five referential actions: CASCADE, SET NULL, RESTRICT, NO ACTION and SET DEFAULT - Each can be configured for "ON DELETE" or "OR UPDATE" events. Dropping a foreign key constraint from a column also cancels the referential actions bound to it.

For example, adding a cascading deletion policy to our users/pets schema (by adding ON DELETE CASCADE to the foreign key definition) would mean that every time a user is deleted, the database will make sure all its "orphan" pets are dropped as well. This can help us to keep a tidy database with no dangling records. If we drop the foreign key, this functionality will be lost, and we have to take it into consideration when planning the schema change.

Prevention​

Preventing accidental constraint drops is easy with Atlas's migrate lint command. With the atlas migrate lint command, users can analyze the migration directory to detect potentially dangerous changes to the database schema. This command may be incorporated in continuous integration pipelines to enable teams to enforce desired policies with regard to schema changes.

When using migrate lint to analyze migrations, users must supply multiple parameters:

  • --dev-url - a URL to a dev database that will be used to simulate the changes and verify their correctness.
  • --dir - the URL of the migration directory, by default it is file://migrations, e.g a directory named migrations in the current working directory.

Changeset detection​

When we run the lint command, we need to instruct Atlas on how to decide what set of migration files to analyze. Currently, two modes are supported:

  • --git-base <branchName>: selects the diff between the provided branch and the current one as the changeset.
  • --latest <n>: selects the latest n migration files as the changeset.

Examples​

Analyze all changes relative to the master Git branch:

atlas migrate lint \
  --dir "file://my/project/migrations" \
  --dev-url "docker://mysql/8/dev" \
  --git-base "master"

Analyze the latest two migration files:

atlas migrate lint \
  --dir "file://my/project/migrations" \
  --dev-url "docker://mysql/8/dev" \
  --latest 2