Do i have to put a foreign key relation to any column in a table with relation with another table? l-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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