Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'users_roles_user_id_foreign' are incompatible.

problem

migration file "20220609090141_create_users_roles_table.ts" failed
migration failed with error: alter table `users_roles` add constraint `users_roles_user_id_foreign` foreign key (`user_id`) references `users` (`id`) - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'users_roles_user_id_foreign' are incompatible.

solution

unify foreign key type.
table.bigInteger('user_id'); -> table.bigInteger('user_id').unsigned();

// users migration file

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  const result = await knex.schema.createTable('users', (table) => {
    table.bigIncrements('id');
    table.string('discord_user_id', 20).notNullable().unique();
    table.string('email', 50).nullable();
    table.string('public_key', 50).notNullable().unique();
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  });
  console.log(result);
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTable('users');
}

// roles migration file

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('roles', (table) => {
    table.increments('id');
    table.string('name', 30).notNullable().unique();
    table.string('permission', 50).notNullable();
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTable('roles');
}

// users_roles migration file

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('users_roles', (table) => {
    table.bigInteger('user_id').unsigned();
    table.integer('role_id').unsigned();
    table.foreign('user_id').references('users.id');
    table.foreign('role_id').references('roles.id');
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTable('users_roles');
}

https://stackoverflow.com/questions/57235138/knex-mysql-error-er-cannot-add-foreign-cannot-add-foreign-key-constraint