Altering constraints in MySQL

In one of my side-projects I have a simple database structure. A users table and links table.

Each user can have many links. So, in my links table I have a column called users_uid which, as you've guessed it, is a foreign key to the users table. Pretty standard setup.

In this project I also have a mig folder, where I keep all the database changes that I make to the schema as .sql files. An example name of such a file would be 00001_create_users_table.sql. As the name suggests, the contents of this file would be a very simple CREATE TABLE users(... query.

Once I'm ready with the development locally, I run those sql files on the server where my app is located. Simple, handmade database migration strategy. Some would argue that it's not "by the book", but it's straight forward and does the job, so I don't care about the "book".

What do I want to do?

Consider the migration file for the links table - 00003_create_links_table.sql.

            
CREATE TABLE IF NOT EXISTS `links` (
    `lid` int NOT NULL AUTO_INCREMENT,
    `link` text NOT NULL,
    `title` text NOT NULL,
    `description` text,
    `user_uid` int NOT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`lid`),
    KEY `fk_users_uid` (`user_uid`),
    CONSTRAINT `fk_users_uid` FOREIGN KEY (`user_uid`) REFERENCES `users` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            
          

Notice the constraint here. It's a simple fk constraint. But if you look closer, you'll see how I have missed out the ON DELETE CASCADE. It's a stupid mistake, because I want the links to be deleted if a user get's removed from the users table.

Now, following my "migration" rules, I have to create a new .sql file just to edit the constraint.

How to alter an existing constraint?

TLDR: You can't.

But, as with most things in programming, there's a workaround.

The idea is to make a two-step move: 1) delete the existing constraint; 2) create a fresh new one.

And to the meat of this article, here's the code to do it. There might be a better way to do it, but at this point I don't really care as it does the job it's suuposed to do. :)))

            
DELIMITER $$

# ------------------ PART 1: DELETE OLD CONSTRAINT ----------------------- #

# First, let's clean the stage, just to make sure there's nothing
# that would interfere with our new proc.
DROP PROCEDURE IF EXISTS DropOldConstraint $$

# Since we're using conditional logic, let's define a proc.
CREATE PROCEDURE DropOldConstraint()
BEGIN
    # Now, i'm querying the information_schema to check if there's any FK
    # with the name 'fk_users_uid' that references the 'users' table.
    # We define a @has_fk variable that holds 0 or 1, depending if there's
    # such a foreign key.
    SELECT CASE WHEN (
        SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS
        WHERE CONSTRAINT_NAME = 'fk_users_uid'
        AND REFERENCED_TABLE_NAME = 'users'
        AND TABLE_NAME = 'links') > 0
    THEN 1
    ELSE 0
    END INTO @has_fk;

    # The easy check - if there's a foreign key found on the schema,
    # remove it, to clear the "stage" for the new constraint.
    IF @has_fk = 1 THEN
        ALTER TABLE `links`
        DROP CONSTRAINT `fk_users_uid`;
    END IF;
END $$

CALL DropOldConstraint() $$

# Clean up this proc. We don't need it anymore.
DROP PROCEDURE IF EXISTS DropOldConstraint $$

# ------------------ PART 2: NEW CONSTRAINT ----------------------- #
# First, let's clean the stage, just to make sure there's nothing
# that would interfere with our new proc.
DROP PROCEDURE IF EXISTS CreateNewConstraint $$

# Since we're using conditional logic, let's define a proc.
CREATE PROCEDURE CreateNewConstraint()
BEGIN
    # Here, the idea is the same. We're going to create the
    # new constraint (fk_links_users_user_uid) only if it doesn't already exist.
    SELECT CASE WHEN (
        SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS
        WHERE CONSTRAINT_NAME = 'fk_links_users_user_uid'
        AND REFERENCED_TABLE_NAME = 'users'
        AND TABLE_NAME = 'links') > 0
    THEN 1
    ELSE 0
    END INTO @has_fk;

    # Following the same logic as above, if the constraint DOESN'T EXIST,
    # create it with ALTER TABLE x ADD CONSTRAINT ...
    IF @has_fk = 0 THEN
        ALTER TABLE `links`
        ADD CONSTRAINT `fk_links_users_user_uid`
        FOREIGN KEY (`user_uid`)
        REFERENCES `users` (`uid`)
        ON DELETE CASCADE;
    END IF;
END $$

CALL CreateNewConstraint() $$

# Clean up this proc. We don't need it anymore.
DROP PROCEDURE IF EXISTS CreateNewConstraint $$

DELIMITER ;