KNEX& MYSQL-错误:ER_CANNOT_ADD_FOREIGN:无法添加外键约束 [英] KNEX & MYSQL - Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

问题描述

我正在将一个整体重构为微服务，并将其中之一的数据库切换到MySQL.我正在使用knex.js清理查询.我需要建立3张桌子.其中一个表只有三列:其自己的ID和两个外键ID:一个来自其他两个表中的每个.

I'm refactoring a monolith into microservices, and switching the DB of one of them to MySQL. I'm using knex.js to sanitize the queries. I need to build 3 tables. One of the tables only has three columns: its own id, and two foreign key ids: one from each of the other two tables.

尝试构建knex.js查询以构建表时，出现标题错误.

When trying to build the knex.js query to build the tables, I get the error in the title.

我尝试使用可以对knex.js查询进行的各种修改来重新排列查询，并尝试进行原始的mysql外键查询.错误仍然存​​在.这是我的js代码:

I've attempted to re-arrange my queries using the various modifications that can be made to knex.js queries, as well as attempted a raw mysql foreign key query. The error persists. Here is my js code:

const knex = require('knex') (CONFIG);

// Build images table
const imagesSchema = () => {
  knex.schema.createTable('images', (table) => {
    table.integer('id').primary();
    table.string('name');
    table.string('src');
    table.string('alt');
    table.string ('category');
    table.string('subCategory');
  })
  .then(console.log('test'));
};

// Build users table
const usersSchema = () => {
  knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('session', 64).nullable();
  })
  .then(console.log('users schema built into DB!'))
  .catch( error => {console.log('cant build the users schema!\n', error)})
}

// Build userhistory table
const userHistorySchema = () => {
  knex.schema.createTable('userhistory', table => {
    table.increments('id').primary();
    table.integer('userid').nullable();
    table.integer('imageid').nullable();

    // add foreign keys:
    table.foreign('userid').references('users.id');
    table.foreign('imageid').references('images.id');
  })
  .then(console.log('userhistory schema built into DB!'))
  .catch( error => {console.log('cant build the userhistory schema!\n', error)})
}

我希望使用userhistory.userid列创建的表指向users.id列，并希望userhistory.imageid列的创建指向images.id列.相反，我收到此错误:

I expect for the table to be created with the userhistory.userid column to point to the users.id column, and for the userhistory.imageid column to point to the images.id column. Instead, I receive this error:

Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

code: 'ER_CANNOT_ADD_FOREIGN',
  errno: 1215,
  sqlMessage: 'Cannot add foreign key constraint',
  sqlState: 'HY000',
  index: 0,
  sql: 'alter table `userhistory` add constraint `userhistory_userid_foreign` foreign key (`userid`) references `users` (`id`)'

创建的表中没有外键，而我希望它们位于外键.

The tables are created without foreign keys where I would like for them to be.

推荐答案

对于MySQL，外键需要定义为unsigned().
因此，您的userhistory模式需要这样设置:

For MySQL the foreign keys need to be defined as unsigned().
So your userhistory schema needs to be set up like this:

knex.schema.createTable('userhistory', table => {
    table.increments('id').primary();
    table.integer('userid').unsigned().nullable();
    table.integer('imageid').unsigned().nullable();

    // add foreign keys:
    table.foreign('userid').references('users.id');
    table.foreign('imageid').references('images.id');
  })
  .then(console.log('userhistory schema built into DB!'))
  .catch( error => {console.log('cant build the userhistory schema!\n', error)})
}

这篇关于KNEX& MYSQL-错误:ER_CANNOT_ADD_FOREIGN:无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！