ALTER TABLE CHANGE is not renaming constraints automatically on the production server

Question

According to the Mysql documentation (which in this case, I believe, also applies to Mariadb), when the command CHANGE is used to rename a column, the constraints are automatically renamed as well:

Doing a local test, I renamed a column, which is foreign key, and the command rotated normally, inclusive renaming the Constraint for the new name:

ALTER TABLE customers_groups CHANGE cliente_id customer_id INT UNSIGNED DEFAULT NULL

However, when running the same command on the server (a Mariadb instance on AWS RDS), the following error appears:

Cannot drop index 'clientes_grupos_cliente_id_foreign': needed in a foreign key constraint

I know I could give one DROP and recreate the constraints, but I’d like to know why you’re not automatically renaming only with CHANGE and only in production.

Answer 1

In this case your problem is not with Mysql, nor with Maria DB. But with Lavarel. Some high-level commands when used actually implement basic actions for us rather than having to do so. This is for both querys in all Dbs, frameworks and programming languages themselves. In your case when you use the CHANGE column command in DB, underneath the cloths it implements a drop and create. Saving the table (and/or the data contained in the column, along with an identifier, in a tree, temporarily in memory. After that it executes the column drop, create the new column and insert the data from the temporary table in the new column according to its corresponding data). Using Lavarel we have a protection to prevent data loss in production environments when performing destructive operations. Depending on the configuration and the size of the column, server, etc. you can find this limitation in DB as well. To perform this type of operation in production and decrease the risks the best and create new tables, and run the dump for the new renamed them later. If you want to run the command, it still runs directly on DB. To run the lavarel, you will need to create a Migration and apply --force, but I’m not entirely sure it will work because I never did it, you may have to perform the actions one by one. Follow lavarel doc passage about destructive operations :

Forcing Migrations to Run in Production Some Migration operations are destructive, which means that they may cause data loss. In order to protect you from executing these commands in the production database, you will be prompted for confirmation before these commands are executed. To force commands and execute without a prompt, use the --force flag:

php Artisan migrate --force

** Another point is to confirm that all the dependencies of the production environment are equal to those of the development environment.

doc link : https://github.com/artesaos/laravel-docs/blob/master/pt_BR/migrations.md

Answer 2

In Postgres I would use a CASCADE, because what is preventing is not the table that is being renamed, but the ones that use it as a dependency.

I don’t have a Mysql environment here, but you could test with a 'ON UPDATE CASCADE'.