Laravel Migrations: Write those Foreign Keys from the start!

By Diego | Laravel

Nov 11

“I see dead records…” has become a fairly common catch phrase for me in some projects lately. The dark practice of delaying the proper writing of the FKs in the migration files. Why devs fall into it and for what purpose is, most of the time,  shady at best.

I never found a good explanation for this practice. Read through, you may be another “Cole” from The Sixth Sense with the potential to save hundreds of lost records…

If you are a Laravel developer, you know what I mean by just reading the title of this post. Even if you are knew to the Laravel world you should have a fairly decent idea. But let’s explain briefly, from the beginning, some of these concepts so we all start from the same place.

A big area, and a core one, in Laravel is it’s database tools and workflow patterns. When you build Laravel applications is more than likely that you will end up using some relational database in it, as the persistent storage of your app’s state. As the development of your app moves forward, your database structure (meaning it’s tables, columns, and datatypes) will change and evolve.

Without Laravel’s set of database tools, you would be interfacing directly with the database engine to make structural changes to our schema.

PKs and FKs in the Database World

Let’s take here a little detour to the Relational Database world and explain a key concept. It will come handy to understand this article’s main idea.

The key point I want to emphasise here is about Primary Keys and Foreign Keys. The real, database engine and enforced, PKs and FKs.

A quick reminder: The primary key of a table is the field, or set of fields, that will allow you to uniquely identify a record on the table and provide the record it’s identity. On the other hand, a foreign key is the mechanism that allows us to make a relationship between two different tables. We include the PK of one table into the other table fields. This/These field(s) in the last table are know as a foreign key.

On the relational database arena there are some very important concepts that you MUST know in order to create a good schema design. Have you heard about the Database normalization concept? If you come from any Computer Science background you should.

For those of you WTF’ing me right now, it’s just a process of organizing the structure of a DB schema (tables and columns) so you reduce the redundancy and increase the integrity of data (less or no data repetition, and security that our data is correct at all times). Go ahead and read some tutorial about Database normalization if you really don’t know what Normalization and a Normal Form means (1NF, 2NF, 3NF etc.). You will learn a key concept that would make your life easier even if you think you won’t need it. Your Laravel app and fellow devs will thank you.

I’m sure that PKs, FKs and normalization come as natural as to go to bed at night for most of you. But what it’s of uttermost importance is to make an explicit SQL definition in your schema that states which columns are the PKs and the FKs. You MUST have, in your raw SQL schema, statements like these:

CREATE TABLE user_roles(
   user_id INT NOT NULL,
   role_id INT NOT NULL,

   PRIMARY KEY(user_id,role_id),
   FOREIGN KEY(user_id) REFERENCES users(user_id),
   FOREIGN KEY(role_id) REFERENCES roles(role_id)
);

Why? Because the DB Engine will enforce that the relationship is met at all times, validating the integrity of the relationship between records. Without the FK constraint on that previous example, anyone can go into the DB and enter whatever INT they want in the user_id or role_id fields. A huge integrity problem, leading to some nasty and hard to find bugs!

PKs and FKs in Laravel migrations

Again, with Laravel we don’t have to worry about creating the PKs and FKs of our tables manually, or write complex SQL statements. We have migration files.

But the practice of writing the proper PKs and FKs remains the same, and we should properly write them in all of our Laravel migrations. You should be doing something like this in each of your migration files:

Schema::create('user_roles', function (Blueprint $table) {
    $table->integer('user_id')->unsigned();
    $table->integer('role_id')->unsigned();

    $table->primary(['user_id', 'role_id']);
    $table->foreign('user_id')->references('id')->on('users');
    $table->foreign('role_id')->references('id')->on('roles);
});


 

“I see dead records…”

In the past few years I’ve seen, and had to work with Laravel code where the devs did not include the proper PKs and FKs. I can’t find a good reason to not do this from the start though.

One might think that by using Laravel, and if we stick to manipulating all of our tables through the Model methods, and if we never do any manual operation directly on the tables (like using DB::insert() for example) we might get away from any possible integrity breaks. But that’s far from the truth.

You can easily have a bug in your code logic, where for some reason, you think you are setting the proper relationship when, indeed, the code is wrong. In this scenario, and without FKs, it will take a really painful debugging session to detect what’s wrong. I’ve seen these cases, and had to perform the debugging myself for long sessions before detecting the real issue!

You might attempt to justify this practice if maybe you are working with a legacy application, that already has a badly designed schema. How to fix this might be a topic for a complete article in its own, but let me briefly say that you should do everything in your power to correct this situation. Refactor, code a migration process, and fix the issue a soon as possible.

Another justification: “I’m migrating from an already messed up database, and even though there’s a new structure in place, I need to import the old and integrity-broken data into the new structure ”. This is ok and a valid reason. But nothing prevents you from making your app, from now on, to behave correctly. Your new database MUST have the proper PKs and FKs. While you perform the initial data migration, and to be able to keep (for historic and archiving purposes) the old data, you can DISABLE specific FKs. Start with all the FKs enabled. As soon as you detect an import failure, write it down, disable the FK, and start again. Very quickly you will have a complete migration process and thorough list of cases to report to your project leader ;). And you will have done the proper thing.

Final words and teaser

I see dead records. Dead records everywhere, moving around the database like normal records. They want me to do something for them. A project manager once told me that I might be one of those rare devs that can see them, and that they might want me to help them out…

Please, help me out and let’s free all of these dead records from our Laravel apps! Take 30 more seconds and write those PKs and FKs right from the start.

Goodbye. Until the next time ;)

Follow

About the Author

I’m a software engineer and work as an independent software developer and consultant. Software development is my passion. I usually find some time to write in between my work and my other interests, sports and PC gaming!