0

I have multiple tables, and some do have columns which are related but I am not certain if I have to put the foreign key relations to all columns which are related.

Here are some of my migrations schemas.

users

Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->unique();
        $table->boolean('role')->nullable( );
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken()->nullable();
        $table->timestamps();
    });

bids

Schema::create('bids', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('loan_id');
        $table->unsignedBigInteger('user_id');
        $table->decimal('interest');
        $table->string('PayType');
        $table->integer('IntervalPay');
        $table->string('GracePeriod');
        $table->timestamps();
        $table->foreign('user_id')
        ->references('id')->on('users')->ondelete('cascade');
        $table->foreign('loan_id')
        ->references('id')->on('loan_request')->ondelete('cascade');
    });

loan_contracts

 Schema::create('loan_contracts', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('lender_id');
        $table->unsignedBigInteger('borrower_id');
        $table->integer('LoanType');
        $table->Biginteger('amount');
        $table->decimal('interest');
        $table->string('GracePeriod');
        $table->string('PayType');
        $table->integer('IntervalPay');
        $table->timestamps();
    });

loan_request

Schema::create('loan_request', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('users_id');
        $table->integer('LoanType');
        $table->Biginteger('amount');
        $table->string('PayType');
        $table->integer('IntervalPay');
        $table->string('GracePeriod');
        $table->timestamps();
        $table->foreign('users_id')
        ->references('id')->on('users')->ondelete('cascade');
    });

If you observe you will see common column names.

Nic3500
  • 8,144
  • 10
  • 29
  • 40
Austin Sanga
  • 13
  • 5
  • Do you _have_ to? No. Should you? Probably! Setting these keys and cascades at the database layer generally means you don't have to do as much in the code. For example, deleting a record; if you don't set the FK/Cascade, you'd need to use `$model = Model::find($id)`, then `foreach($model->relationship as $relation) { $relation->delete(); }`, then `$model->delete()`. And repeat that for each nested relationship, etc. If you set the FK/Cascade, `$model->delete()` will do everything for you. – Tim Lewis May 13 '22 at 16:43
  • If data integrity has value to the app, then yes. If data integrity is of no value to the app, then no. – The Impaler May 13 '22 at 17:30

2 Answers2

0

You do it because there are relations - so you can click user_id in phpmyadmin/whatever and jump to proper userid. In order to delete, you remove the user and mysql removes relations without additional harassment.

In short - convenience. You don't need leftovers from tables that act on non-existing users.

Elboyler
  • 87
  • 4
0

Well, in fact, when you use $table->foreign('user_id') besides creating a key, Laravel is also creating the regarding index for that foreign key.

The main benefit of foreign keys is that they enforce data consistency, meaning that they keep the database clean. It is true that foreign keys will impact INSERT, UPDATE, and DELETE statements because they are data checking, but they improve the overall performance of a database.

An index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. Searching for data based on a column that is part of the index will allow us to make use of the index to quickly access the record.

It's not just about checking if a related record/relationship exists to keep consistency. If you work with millions of records, you will notice this improvement easily (A query performance can be improved from 20secs down to a few milliseconds).

So the short answer is yes, you should define your foreign keys.

There is an interesting (and accepted) answer it this post https://stackoverflow.com/a/1130/2330666

Luciano
  • 2,052
  • 1
  • 16
  • 26