-1

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
  • 13
  • 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

2 Answers2

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
  • 2,013
  • 2
  • 21
  • 42
gbalduzzi
  • 9,356
  • 28
  • 58
  • 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
0

Try this:

$table->unsignedInteger('payment_id');

$table->foreignId('payment_id','monthlies')->.constrained('monthlies');
                 
$table->foreignId('payment_id','items')->constrained('items');

In this code foreignId(column_name,'name_of_key').

Hope this helps.

Ethan
  • 876
  • 8
  • 18
  • 34
Duc Tong
  • 31
  • 1
  • 4