Search code examples
javascriptnode.jsknex.js

kenxjs - Reference one column to two columns

How to fix this code? I want to reference recordId to ids colums in the tickets and packages tables.

createTable(`payments`, function(t){
        t.increments();
        t.integer('userId').references('id').inTable(`users`).unsigned().onDelete('cascade');
        t.integer('recordId')
        .references('id').inTable(`tickets`).unsigned().onDelete('cascade');
        t.integer('recordId')
        .references('id').inTable(`packages`).unsigned().onDelete('cascade');
        t.string('peyFro');
        t.string('gateway').notNullable();
        t.string('authority').notNullable();
        t.integer('amount').notNullable();
        t.string('status').notNullable();
        t.dateTime('createdAt').defaultTo(knex.fn.now());
    })
Solution

  • Try the foreign function:

    t.integer('recordId').unsigned();
t.foreign('recordId','payment_ticket_fk').references('tickets.id').onDelete('cascade');
t.foreign('recordId','payment_package_fk').references('packages.id').onDelete('cascade');

    Linking one FK column to multiple PKs seems like bad design (that might lead to bad data) though. Perhaps you could just have two different FK columns.