One to many Definition and Foreign Key

Published 3 weeks ago by Opulido

I have seen on tutorials people declaring the foreign key on one to many scenarios and others do not use it. Which is the way to go? Should we always use the foreing key definition, for example:

$table->integer('user_id')->unsigned()->index()->nullable();
$table->foreign('user_id')->references('id')->on('users');

and second question, how to reference the model class in the relationship:

    return $this->hasMany('App\Comment');      This one
    return $this->hasMany(Comment::class);     Or this one

Regards, Omar

Sergiu17
Sergiu17
3 weeks ago (190,650 XP)

Of course go with foreign key, read definition - https://en.wikipedia.org/wiki/Foreign_key

return $this->hasMany(Comment::class);

Go with this approach, result is the same, but IDE understands it better, in PHPStorm when you CTRL+CLICK on Comment class, it redirects to Comment class

Digitalized

I would say always use the foreign keys where possible, as it means you can always be confident in the integrity of your database. You won't end up with orphan records when related records get deleted etc.

I would always use the following syntax:

return $this->hasMany(Comment::class);

If you used the syntax above in Sublime text editor, you could right click on the class name and instantly navigate to the Comment class, which is an obvious advantage. Also it's easier to read in my opinion due to the way the text editor marks up the code.

Vilfago

To have a counter argument, I heard to avoid foreign key as most as possible, to improve the speed of the database as it could take a lot more resources on insert/update/delete.

The integrity constraint should be ensure with some control in your code, that you obviously will do before inserting/updating some data. If the error come from the database, it's "too late".

pantox
pantox
3 weeks ago (60,110 XP)

As always it depends on the requirements

Thanks @Vilfago for the counter argument. I found an interesting article about it https://dataedo.com/blog/why-there-are-no-foreign-keys-in-your-database-referential-integrity-checks

Opulido

Well as you guy mentored it depends on requirements and as long as the integrity of the data is taken care by the app avoiding foreign keys should be the way to go. Also, speed access is not determine by the foreign key but by the indexes in the table. If we are talking about millions kg records depending on the requirement or business rule the table could have a primary index and Simenon others.

What is your take regarding speed / performance and primary key and indexes.

$table->integer('user_id')->unsigned()->index()->nullable();

So, should the above line be kept in the migration file?

Regards

Please sign in or create an account to participate in this conversation.