Non-Nullable Table Additions

Order is important. In your migration file add your column with a default value which should propagate to all rows with the default value. Then add the code for the foreign key.

I had issues when I started using laravels migrations with the sequence the different migrations ran. Specifically setting foreign keys to early.

Keep in mind too eloquent and relations don't rely on foreign keys, indexing works too with no performance impact unless you need sql cascade features of course

If you are adding foreign keys, the referenced columns must match type definition exactly, and the field you're applying the index to must have valid data in it i.e. no values exist in the column you are creating a key on that does not exist in the foreign table.

If you have existing data, you'll need to do one of two things:

1. Create and seed the foreign table with the existing index data first then create the migration to add the foreign keys
2. Disable foreign key checks - DB::statement('SET FOREIGN_KEY_CHECKS=0'); before your migration, DB::statement('SET FOREIGN_KEY_CHECKS=1'); after it.

The first option is (usually) the preferable one; it ensures integrity of your foreign key constraints. The second option will allow you to run the (invalid) migrations, but may lead to issues down the track when updating rows that contain invalid key data.

Generally speaking, the foreign key will be an integer referencing a record in another table that has more information such as a text description or key. If you're using a string (which is a less efficient index than what an unsigned integer would be), I would consider the possibility of tackling option 1.

Before the migration runs:

• Select all the unique values to be indexed
• Insert them into the foreign table
• Track the id of the inserted values
• Update the relating table's data to match the new id values
• Update the relating table's structure to be a nullable, unsigned integer
• Apply the foreign key index to the relating table

You can have multiple Schema::table() calls inside your up and downmigration methods, so you can conceivable run all of the above within a single migration.

How the foreign data was created will determine the best way forward here.

Thank you both for your answers, I apologize for taking so long to follow up - holidays and family! Anyway, I think I understand the general approach being outlined here, but I don't see how this works well with the artisan commands to execute the seed/migration.

Let's say I first make a migration to create the new foreign table, then a seed file to fill that table with the starting data needed to fulfill the default set in the second migration that adds the foreign key constraint to the primary table.

The issue I see is that if I now run artisan migrate, it will run the first to create the foreign table structure, then immediately run the second adding the constraint - but before I would have an option to run db:seed that would actually insert the data required to fulfill the constraint.

In this type of scenario is it normal to have seed data inserted during the execution of your migration script?

I believe I found my answer by simply calling the specific seed class I need from within the migration as follows:

Artisan::call('db:seed', [ '--class' => MigrationSpecificSeeder::class, ]);

Taken from: https://laracasts.com/discuss/channels/general-discussion/run-seeds-with-migrations-together

If there's a better way, I'd still like any feedback.

Yeah, any dependent stuff like that I tend to put into the migration itself.

That said, you should still be able to create migrations that create foreign keys without any data in any tables. You'll only run into issues at that point if your foreign and reference fields don't match exactly. One common pitfall is having the foreign key not being unsigned() - where an auto-incrementing will always be unsigned.

If there's ever a change that you'll have a record in the reference table without establishing a relationship - say you create a post, but don't give it a status - then you should make the foreign key column nullable() as well.