Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

chkltlabs's avatar

Cleaning up a poorly designed database

Salud, everyone.

I am working within a database that was set up by a php/laravel newbie @ a startup. The person in question has moved on, and its now my job to clean up their naive choices.

Most every table does not use an auto incrementing ID, opting rather for unsigned int values that were statically set in seeders designed to be run in production. (i know, ew). These ID values also tend to very consistently have foreign key constraints that are causing issues when I am going through and trying to bring everything back into line with laravel convention.

I am using the Schema facade to run $table->id()->change() commands on most tables, and the corresponding relations get changed with $table->unsignedBigInteger('whatever_id')->change().

When my migration runs, however, I get General error: 3780 Referencing column 'whatever_id' and referenced column 'id' in foreign key constraint 'table_whatever_id_foreign' are incompatible

Given that they WILL BE compatible once all the operations in my migration run, how can I disable this check? (i am already employing Schema::disableForeignKeyConstraints()) Is my only option to delete and remake the foreign key constraint?

Thx, E

0 likes
3 replies
mabdullahsari's avatar

The problem here is that a BigInt is referencing an Int. Change them to BigInt's as well and it should work. Foreign Keys do not require an AI ID.

FWIW, you should migrate step by step. First change them to Bigs, then change them to become AI etc.

chkltlabs's avatar

Your suggestion does not fly @mabdullahsari . The existing foreign key constraints step in and stop all operations, regardless of which ones kick off first or what intermediate steps are taken. Ill try dropping all the relevant constraints and remaking them instead.

Tray2's avatar

I feel for you, this is never an easy task, and to perform it with migrations in Laravel does make it even trickier. What I would do, or at least attempt to do is.

  1. Export the complete database to SQL using https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html
  2. Copy all the existing data into backup tables CREATE TABLE table1_backup AS (SELECT * FROM table1);
  3. Drop all the non backup tables
  4. Create the new table structure for all the tables (Migrations)
  5. Copy the data back from the backup tables INSERT INTO table1 SELECT col2, col3, col4 FROM table1_backup;
  6. All tables that has foreign keys will need to be checked against the backup tables so that the referencial integrity isn't violated.

I know that this will be very time consuming, but it's probably the only way to go.

1 like

Please or to participate in this conversation.