Search code examples
node.jspostgresqlexpressknex.js

How to create table relationships Knex.js/express through migration?

I have all the tables I needed to create for my back-end application, but when I try to add in foreign keys for relationships it throws error when the migration is called. the database is postgreSQL Goal: I would like to make 1 more table that references all the foreign keys linking all the tables to the 1 table. like the example blow.

exports.up = function (knex) {
  return knex.schema.createTable('Overview', (table) => {
    table.uuid('overview_id').primary()
    table
      .foreign('user_id')
      .references('user_id')
      .inTable('users')
      .onDelete('cascade')
    table
      .foreign('player_id')
      .references('player_id')
      .inTable('playerStats')
      .onDelete('cascade')
    table
      .foreign('registration_id')
      .references('registration_id')
      .inTable('registration')
      .onDelete('cascade')
    table
      .foreign('planet_id')
      .references('planet_id')
      .inTable('planetList')
      .onDelete('cascade')
    table
      .foreign('buildings_id')
      .references('buildings_id')
      .inTable('buildings')
      .onDelete('cascade')
    table
      .foreign('production_id')
      .references('production_id')
      .inTable('production')
      .onDelete('cascade')
    table
      .foreign('research_id')
      .references('research_id')
      .inTable('research')
      .onDelete('cascade')
    table
      .foreign('player_fleet_id')
      .references('player_fleet_id')
      .inTable('playerFleet')
      .onDelete('cascade')
    table
      .foreign('player_defense_id')
      .references('player_defense_id')
      .inTable('playerDefense')
      .onDelete('cascade')
  })
}

ERD

Solution 
  • exports.up = function (knex) {
  return knex.schema.createTable('Overview', (table) => {
    table.uuid('overview_id').primary()
    table.integer('user_id').notNullable()
    table
      .foreign('user_id')
      .references('user_id')
      .inTable('users')
      .onDelete('cascade')
    table.integer('player_id')
    table
      .foreign('player_id')
      .references('player_id')
      .inTable('playerStats')
      .onDelete('cascade')
    table.integer('registration_id')
    table
      .foreign('registration_id')
      .references('registration_id')
      .inTable('registration')
      .onDelete('cascade')
    table.integer('planet_id')
    table
      .foreign('planet_id')
      .references('planet_id')
      .inTable('planetList')
      .onDelete('cascade')
    table.integer('buildings_id')
    table
      .foreign('buildings_id')
      .references('buildings_id')
      .inTable('buildings')
      .onDelete('cascade')
    table.integer('production_id')
    table
      .foreign('production_id')
      .references('production_id')
      .inTable('production')
      .onDelete('cascade')
    table.integer('research_id')
    table
      .foreign('research_id')
      .references('research_id')
      .inTable('research')
      .onDelete('cascade')
    table.integer('player_fleet_id')
    table
      .foreign('player_fleet_id')
      .references('player_fleet_id')
      .inTable('playerFleet')
      .onDelete('cascade')
    table.integer('player_defense_id')
    table
      .foreign('player_defense_id')
      .references('player_defense_id')
      .inTable('playerDefense')
      .onDelete('cascade')
    table.integer('store_id')
    table
      .foreign('store_id')
      .references('store_id')
      .inTable('store')
      .onDelete('cascade')
  })
}