database

Rename foreign keys in MySQL

Foreign keys are essential for maintaining the referential integrity in relational databases. Sometimes, their names change, for example when you use Percona’s pt-online-schema-change and suddenly all your foreign keys have an underscore prefix.

MySQL does not provide an option to rename foreign keys on the fly. Typically the foreign key needs to be dropped and re-created. Adding a foreign key can be expensive when the index on the column does not already exist. In the case of renaming a foreign key, the index on the key column does exist already, thus renaming an foreign key is a lot cheaper.

The text snippets below are from the the documentation.

MySQL supports to ADD and DROP a foreign key within one ALTER TABLE statement. This only works when you use the ALGORITHM=INPLACE option.

Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE but not for ALTER TABLE ... ALGORITHM=COPY.

INPLACE makes the ALTER statements much faster, but there are additional constraints.

INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.

That is important because

ALTER TABLE operations that use the COPY algorithm wait for other operations that are modifying the table to complete. After alterations are applied to the table copy, data is copied over, the original table is deleted, and the table copy is renamed to the name of the original table. While the ALTER TABLE operation executes, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table started after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table. The temporary copy of the table is created in the database directory of the original table unless it is a RENAME TO operation that moves the table to a database that resides in a different directory.

The problem is that ALGORITHM=INPLACE only works when the foreign_key_checks are disabled. Only then we can we can use a non-locking online DDL change. The key to this is using LOCK=NONE as additional setting. Specifying the lock mode is in general good practice. If an operation cannot be done without a lock (LOCK=NONE) or only with an exlusive lock (LOCK=SHARED), then the ALTER statement would fail with an error. So you do not risk to lock the database when it could not be avoided.

Retrieving odd foreign key names

The following query retrievs all foreign keys with an underscore prefix and produces the ALTER table statements we desperately need for renaming the FKs. It retrieves only the foreign keys from the sakila database.

SELECT
    CONSTRAINT_SCHEMA,
    TABLE_NAME,
    CONSTRAINT_NAME old_name,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME,
    CASE
        WHEN CONSTRAINT_NAME REGEXP '^\_{1}[A-Za-z0-9]{2}.*$'
            THEN SUBSTR(CONSTRAINT_NAME, 2, LENGTH(CONSTRAINT_NAME))
        WHEN CONSTRAINT_NAME REGEXP '^\_{2}[A-Za-z0-9]{2}.*$'
            THEN SUBSTR(CONSTRAINT_NAME, 3, LENGTH(CONSTRAINT_NAME))
        END AS new_name,
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ADD CONSTRAINT ', (
        CASE
            WHEN CONSTRAINT_NAME REGEXP '^\_{1}[A-Za-z0-9]{2}.*$'
                THEN SUBSTR(CONSTRAINT_NAME, 2, LENGTH(CONSTRAINT_NAME))
            WHEN CONSTRAINT_NAME REGEXP '^\_{2}[A-Za-z0-9]{2}.*$'
                THEN SUBSTR(CONSTRAINT_NAME, 3, LENGTH(CONSTRAINT_NAME))
            END), ' FOREIGN KEY (', COLUMN_NAME, ') REFERENCES ', REFERENCED_TABLE_SCHEMA, '.',
           REFERENCED_TABLE_NAME, '(', REFERENCED_COLUMN_NAME,
           '), ALGORITHM=INPLACE, LOCK=NONE;') AS alter_statement_for_new_name,
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ADD CONSTRAINT ', CONSTRAINT_NAME,
           ' FOREIGN KEY (', COLUMN_NAME, ') REFERENCES ', REFERENCED_TABLE_SCHEMA, '.',
           REFERENCED_TABLE_NAME, '(', REFERENCED_COLUMN_NAME,
           '), ALGORITHM=INPLACE, LOCK=NONE;') AS alter_statement_for_old_name,
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME,
           ', ALGORITHM=INPLACE, LOCK=NONE;') AS drop_statement,
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ADD CONSTRAINT ', (
        CASE
            WHEN CONSTRAINT_NAME REGEXP '^\_{1}[A-Za-z0-9]{2}.*$'
                THEN SUBSTR(CONSTRAINT_NAME, 2, LENGTH(CONSTRAINT_NAME))
            WHEN CONSTRAINT_NAME REGEXP '^\_{2}[A-Za-z0-9]{2}.*$'
                THEN SUBSTR(CONSTRAINT_NAME, 3, LENGTH(CONSTRAINT_NAME))
            END), ' FOREIGN KEY (', COLUMN_NAME, ') REFERENCES ', REFERENCED_TABLE_SCHEMA, '.',
           REFERENCED_TABLE_NAME, '(', REFERENCED_COLUMN_NAME, ') ', ', DROP FOREIGN KEY ',
           CONSTRAINT_NAME,
           ', ALGORITHM=INPLACE, LOCK=NONE;') AS alter_statement_for_new_name_combined,
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ADD CONSTRAINT ', CONSTRAINT_NAME,
           ' FOREIGN KEY (', COLUMN_NAME, ') REFERENCES ', REFERENCED_TABLE_SCHEMA, '.',
           REFERENCED_TABLE_NAME, '(', REFERENCED_COLUMN_NAME, ') ', ', DROP FOREIGN KEY ',
           (CASE
                WHEN CONSTRAINT_NAME REGEXP '^\_{1}[A-Za-z0-9]{2}.*$'
                    THEN SUBSTR(CONSTRAINT_NAME, 2, LENGTH(CONSTRAINT_NAME))
                WHEN CONSTRAINT_NAME REGEXP '^\_{2}[A-Za-z0-9]{2}.*$'
                    THEN SUBSTR(CONSTRAINT_NAME, 3, LENGTH(CONSTRAINT_NAME))
               END), ', ALGORITHM=INPLACE, LOCK=NONE;') AS alter_statement_for_old_name_combined
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = 'sakila'
    AND ASCII(CONSTRAINT_NAME) LIKE ASCII('_%')
    AND REFERENCED_TABLE_NAME IS NOT NULL ;

Changing the foreign keys on the fly

When you can ensure that no changes are happening to the database containing the foreign key you want to rename, the operation itself is only a metadata change and very fast. So it is worth shutting down the applications writing data, ensure that all connections are closed and then run the ALTER statements with the foreign_key_checks disabled.

The proper ALTER statement has the following structure

SET SESSION foreign_key_checks = 0;
ALTER TABLE sakila.Actor ADD CONSTRAINT fk_movie_actor FOREIGN KEY (movie_id) REFERENCES sakila.Movie(id), DROP FOREIGN KEY _fk_movie_actor, ALGORITHM=INPLACE, LOCK=NONE;
SET SESSION foreign_key_checks = 1;

Create an ER Diagram of an Existing SQLite Database (or many other RDBMS)

Visualisation helps solving problems and is therefore an important tool database design. Many database providers have their product specific tools for re-engineering existing schemata, but self-contained, serverless, embedded relational database management systems (RDBMS) such as SQLite often come without much tooling support. The extremely small footprint of SQLite provides a very powerful tool for implementing database driven applications without the hassle of database administration, user privilege management and other demanding tasks that come with more complex systems. There does not exist a workbench-like tool for SQLite, but we can use the open source SchemaCrawler for analysing database schemata and table relationships. The tool provides a plethora of commands and options, in this post we will only cover the diagramming part, which allows creating ER diagrams of the table.

After downloading and extracting the tool to your local drive, you can find a lot of examples included. The tool can handle SQLite, Oracle,  MS SQL Server, IBM DB2, MySQL, MariaDB, Postgres and Sybase database servers and is therefore very versatile. You will need Java 8 in order to run it. Have a look at the script below, which creates a PNG image of the database schema of the Chinook test database.

#!/bin/bash
# The path of the unzipped SchemaCrawler directory
SchemaCrawlerPATH=/media/stefan/Datenbank/schemacrawler-14.05.04-main
# The path of the SQLite database
SQLiteDatabaseFILE=/media/stefan/Datenbank/ChinookDatabase1.4_CompleteVersion/Chinook_Sqlite.sqlite
# The type of the database system.
RDBMS=sqlite
# Where to store the image
OutputPATH=/media/stefan/Datenbank/ER.png
# Username and password need to be empty for SQLite
USER=
PASSWORD=

java -classpath $(echo ${SchemaCrawlerPATH}/_schemacrawler/lib/*.jar | tr ' ' ':') schemacrawler.Main -server=${RDBMS} -database=${SQLiteDatabaseFILE} -outputformat=png -outputfile=${OutputPATH} -command=graph -infolevel=maximum -user=${USER} -password=${PASSWORD}

The SchemaCrawlerPATH variable contains the path to the directory where we unzipped the SchemaCrawler files to. This is needed in order to load all the required libraries into the classpath below. We then specify the SQLite database file, define the RDBMS and provide an output path where we store the image. Additionally, we provide an empty user name and password combination. SQLite does not provide user authentication, thus those two parameters need to be empty, SchemaCrawler simply ignores them. Then we can execute the command and the tool generates the PNG of the ER diagram for us.

You can find a lot of examples also online, which gives you an overview of the features of this tool. One of the main purposes of SchemaCrawler is to generate diffable text outputs of the database schemata. In combination with a source code version management tool such as Git or Subversion, you can create clean and usable reports of your databases and keep track of the changes there. You can retrieve an overview of the options  with the following command.

java -classpath $(echo ${SchemaCrawlerPATH}/_schemacrawler/lib/*.jar | tr ' ' ':') schemacrawler.Main -?

You can also HTML reports with the following command:

java -classpath $(echo ${SchemaCrawlerPATH}/_schemacrawler/lib/*.jar | tr ' ' ':') schemacrawler.Main -server=${RDBMS} -database=${SQLiteDatabaseFILE} -outputformat=html -outputfile=report.html -command=details -infolevel=maximum -user=${USER} -password=${PASSWORD}

Other available output formats are plain text, CSV or JSON.