sequelize: Sqlite remove/change/rename column don't pass foreign key references along in the new table

What you are doing?

I’m trying to rename a column on an Sqlite db. I use renameColumn on the QueryInterface that, in the Sqlite case, is going to call renameColumn on the SQLiteQueryInterface.

What do you expect to happen?

I expect that:

a temporary table is created equal to the first one (except the renamed column) including foreign key references.
data is copied over to the temporary table
old table is deleted
a new table is created equal to the first one (except the renamed column) including foreign key references.
data is copied over to the new table

What is actually happening?

a temporary table is created equal to the first one (except the renamed column) NOT including foreign key references.
data is copied over to the temporary table
old table is deleted
a new table is created equal to the first one (except the renamed column) NOT including foreign key references.
data is copied over to the new table

This is the original table:

CREATE TABLE `matches` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hash` VARCHAR(200) NOT NULL UNIQUE, `alertId` INTEGER REFERENCES `alerts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

This is the table after the renameColumn on ‘hash’ -> ‘foo’

CREATE TABLE `matches` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `foo` VARCHAR(200) NOT NULL, `alertId` INTEGER)

Dialect: sqlite Database version: 3.9.x Sequelize version: 3.24.x

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 5
Comments: 18 (10 by maintainers)

Commits related to this issue

fix(sqlite): describeTable now returns unique constraint, fixes #7078 — committed to dyc3/sequelize by dyc3 4 years ago
fix(sqlite): describeTable now returns unique constraint, fixes #7078 — committed to dyc3/sequelize by dyc3 4 years ago
fix(sqlite): describeTable now returns unique constraint, fixes #7078 — committed to dyc3/sequelize by dyc3 4 years ago
fix(sqlite): describeTable now returns unique constraint, fixes #7078 — committed to dyc3/sequelize by dyc3 4 years ago
fix(sqlite): describeTable now returns unique constraint, fixes #7078 — committed to dyc3/sequelize by dyc3 4 years ago
fix(sqlite): describeTable now returns references, fixes #7078 — committed to dyc3/sequelize by dyc3 4 years ago
fix(sqlite): describeTable now returns unique constraint, fixes #7078 — committed to dyc3/sequelize by dyc3 4 years ago
fix(sqlite): describeTable now returns references, fixes #7078 — committed to dyc3/sequelize by dyc3 4 years ago

Most upvoted comments

I am avoiding similar problem in the following bad patch code. This patch modify queryInterface.describeTable method.

function up(queryInterface) {
  let backupDescribeTable = queryInterface.describeTable;
  queryInterface.describeTable = async function (tableName, options) {
    let tableDatas = await backupDescribeTable.call(queryInterface, tableName, options);
    // add auto increment to id column
    tableDatas.id.autoIncrement = true;
    // get foreign key info
    let foreignKeyDatas = (await queryInterface.sequelize.query(`PRAGMA FOREIGN_KEY_LIST("${tableName}");`))[0];
    for (let foreignKeyData of foreignKeyDatas) {
      let tableData = tableDatas[foreignKeyData.from];
      tableData.references = {model: foreignKeyData.table, key: foreignKeyData.to};
      tableData.onDelete = foreignKeyData.on_delete;
      tableData.onUpdate = foreignKeyData.on_update;
    }
    return tableDatas;
  };
  change or remove or renameColumn(...);
}

This patch can restoration of foreign keys. AutoIncrement is force set to id column.

yikescojp on Feb 1, 2018

Still an issue.

mwohlert on Jul 24, 2019