Skip to main content

Detecting inline REFERENCES clauses (MY102)

Introduction​

This document describes in detail the detection of inline REFERENCES clauses for MySQL.

According to the MySQL Documentation:

MySQL parses but ignores "inline REFERENCES specifications" (as defined in the SQL standard) where the references are defined as part of the column specification

This means that despite the fact that in many databases users can define foreign key constraints inline with the column definition as such:

CREATE TABLE pets (
    owner_id int REFERENCES users(id)
);

The database will silently ignore the REFERENCES users(id) clause.

Impact​

Referential integrity​

REFERENCES clauses are used to define referential constrains (foreign keys) that allow application developers to rely on the database to maintain referential integrity.

A developer that successfully applied the CREATE TABLE statement above may wrongfully believe that the database will reject any inserts of records that refer to non-existing records. As a result, other means of validation may not be implemented, which may lead to a situation where referential integrity is lost.

Prevention​

asciicast

Preventing migrations with inline REFERENCES clauses 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>: which selects the diff between the provided branch and the current one as the changeset.
  • --latest <n> which 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 "mysql://root:pass@localhost:3306/dev" \
  --git-base "master"

Analyze the latest 2 migration files:

atlas migrate lint \
  --dir "file://my/project/migrations" \
  --dev-url "mysql://root:pass@localhost:3306/dev" \
  --latest 2