asdrubalivan asdrubalivan - 1 year ago 187
Node.js Question

How do I drop a foreign key using Sequelize.js?

I know this can be possible by using a raw query like the one used in this question to remove a constraint, however, is there any built-in method to drop foreign keys from Sequelize.js?

Answer Source

Not for now - https://github.com/sequelize/sequelize/issues/5212

I use this approach, methods from QueryGenerator add ticks for you

let dropFKSQL = queryInterface.QueryGenerator.dropForeignKeyQuery("tableName", "foreignKey")
return queryInterface.sequelize.query(dropForeignKeySQL);

But auto generated foreign keys with postfix _ibfk_{index} (by MySQL itself) amd _idx (by sequelize) are a huge pain in the ass. You can use this method to get FK dynamically:

public getForeignKeyName(tableName: string, columnName: string, opts: { queryInterface: QueryInterface }): Promise<string> {
    let sqlz = opts.queryInterface.sequelize;
    let sql = `
        SELECT CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE REFERENCED_TABLE_SCHEMA = '${dbSchema}'
          AND TABLE_NAME = '${tableName}'
          AND COLUMN_NAME = '${columnName}'
    `;

    return sqlz.query(sql, { type: sqlz.QueryTypes.SELECT })
        .then((result: { CONSTRAINT_NAME: string }[]) => {
            if (!result || !result[0] || !result[0].CONSTRAINT_NAME) {
                return null;
            }

            return result[0].CONSTRAINT_NAME;
        });
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download