Adding unique field to MySQL table with existing records

A

Another quick tip, this time around adding a uniqued field to a MySQL table that already has data in it using a Laravel migration.

Take the below example:

A real simple migration to add a slug field to an existing posts table. If you run this migration with existing data in the posts table, you’ll come across this error:

This is because you’re not giving each row in the table a unique value to populate slug with, so it’s failing on the 2nd row (as it has the same empty string value as the first row). You can’t set a default value for this field either, as you’ll end up with the same error.

The workaround to this is relatively simple, but may not be immediately obvious: perform the operation in steps. Create the slug field without the unique constraint, populate it with unique values, and finally add the unique constraint.

Migrations can be extremely useful for migrating your data as well as your schema. Don’t be shy to use the DB facade inside them, but try to stay clear of using Eloquent models directly. Your migrations should be immutable, so that once they’re run in production they never change. Eloquent models, by nature, change over time along with your codebase and adapt their behavior to suit new business requirements, which can lead to your old migrations subtly changing their behavior as well.

About the author

Chris

Chris is a software engineer from Scotland. He can usually be found working on web applications for Intouch Insight using Laravel and Angular, but tends to dabble a lot with other technologies in his free time.

Recent Posts

Archives

Meta