An important fix for composite primary keys

I've talked before about the importance of adding a primary key to all tables when using MySQL replication. I even add them to the default Laravel tables that don't come with primary keys.

But recently I was migrating an app to use a managed MySQL database (with replication) on Digital Ocean, and I bumped into this same error where a migration fails due to a lack of a primary key.

I was confused at first, because all of my tables did have a primary key. I've done this before, so I knew to avoid that issue. So what was going on?

After some digging and step debugging, I realized that when the primary key is a composite key (made up of more than one field), Laravel actually issues two SQL statements: one to create the table (without the primary key) and then a follow-up statement to add the composite primary key.

But because the replicated database enforced the use of a primary key on all tables, my migration was still failing with the same message about missing a primary key.

Thankfully, Laravel 10.38.0 introduced a fix for this issue, and combined those two SQL statements into one, which solved my problem without any gross hacks to turn off the primary key enforcement.

The project I was migrating was on 10.32.1, so it wasn't horribly out of date, but just a few weeks behind and missing that important improvement.

Here to help,

Joel

P.S. Do you want to migrate from a self-managed MySQL server to something managed and replicated on Amazon or Digital Ocean? I've done this so many times, let me help!