0

In my project i have Journals table, Monthlies table, Loan table, Investment table. Journal table has payment_id which i want to give foreign key for the monthlies, loan and investment tables ID. I have tried this but in migration it occurs error "duplicate key on write or update"

what i want to do is , I am trying to insert loan, investment, monthlies id on journals table payment_id when loan, investment, monthlies are created. I can insert only one tables id on journals table payment_id (through foreign key relationship in migration) not multiple...how can i do that? 

public function up()
{
    Schema::create('journals', function (Blueprint $table) {
        $table->increments('id');
        $table->double('amount');
        $table->integer('payment_id')->unsigned();
        $table->enum('payment_format', ['monthly', 'investment', 'loan', 'income', 'expense', 'others']);
        $table->string('short_description');
        $table->integer('created_by')->unsigned();
        $table->integer('updated_by')->unsigned();
        $table->dateTime('deleted_at');
        $table->timestamps();

        $table->foreign('payment_id')->references('id')->on('monthlies')
            ->onUpdate('cascade')->onDelete('cascade');

        $table->foreign('payment_id')->references('id')->on('investments')
            ->onUpdate('cascade')->onDelete('cascade');
        $table->foreign('payment_id')->references('id')->on('loans')
            ->onUpdate('cascade')->onDelete('cascade');

        $table->foreign('created_by')->references('id')->on('users')
            ->onUpdate('cascade')->onDelete('cascade');
        $table->foreign('updated_by')->references('id')->on('users')
            ->onUpdate('cascade')->onDelete('cascade');

    });
}
Imtiaz Ahmed
  • 15
  • 2
  • 8
  • That's not a correct approach on SQL, not a laravel problem. see:https://stackoverflow.com/questions/15547276/it-is-possible-to-reference-one-column-as-multiple-foreign-keys/15549622 – gbalduzzi Apr 16 '19 at 15:08

1 Answers1

0

You can't do that in laravel. The same column can not be used as a foreign key to 3 other tables. However, you can do this in Laravel without using foreign keys on the database level.

Your migration:

Schema::create('journals', function (Blueprint $table) {
    $table->increments('id');
    $table->double('amount');
    $table->integer('payment_id')->unsigned();
    $table->enum('payment_format', ['monthly','investment','loan','income','expense','others']);
    $table->string('short_description');
    $table->integer('created_by')->unsigned();
    $table->integer('updated_by')->unsigned();
    $table->dateTime('deleted_at');
    $table->timestamps();

    $table->foreign('created_by')->references('id')->on('users')
        ->onUpdate('cascade')->onDelete('cascade');
    $table->foreign('updated_by')->references('id')->on('users')
        ->onUpdate('cascade')->onDelete('cascade');

});

In your Journal model, define the relationship to your payment:

public function payment() {
    switch($this->payment_format) {
        case 'loan':
            return $this->belongsTo(Loan::class, 'payment_id');
        case 'monthly':
            return $this->belongsTo(Monthly::class, 'payment_id');
        case 'investment':
            return $this->belongsTo(Investment::class, 'payment_id');
        default:
            // ??
            break;
    }
}

Now, by calling $journal->payment you get the proper object back based on the value in your payment_format field.

loic.lopez
  • 1,850
  • 2
  • 18
  • 37
gbalduzzi
  • 8,482
  • 27
  • 54
  • Thanks but basically i am trying insert loan, investment, monthlies id on journals table payment_id when loan, investment, monthlies are created. I can insert only one tables id on journals table payment_id (through foreign key relationship in migration) not multiple...how can i do that? – Imtiaz Ahmed Apr 16 '19 at 15:58
  • Sorry, I can't understand your question – gbalduzzi Apr 16 '19 at 16:17
  • brother In my project when i create Loan, Investment, Monthly then i want to create journal for that and in journal tables foreign_key should have the id of those tables( loan, investment Monthly) `$table->foreign('payment_id')->references('id')->on('monthlies') ->onUpdate('cascade')->onDelete('cascade');` bu writing this i can store monthly table id on payment_id but cant store rest of the tables id on payment_id...Any other way i can do this?? as i can't assign same foreign key for multiple tables column – Imtiaz Ahmed Apr 17 '19 at 11:00