Cli: Could not create constraint or index. See previous errors.

[v1d3rm3]

Additional info: If the table that will be referenced by a table of migration already exists, then sequelize create foreign key successfuly, but if the table that will be referenced by a table created in migration before, then it not works.

What you are doing?

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    return await queryInterface.createTable(
      'Comprovante',
      {
        IdComprovante: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        Descricao: {
          type: Sequelize.STRING,
          allowNull: false,
        },
        Valor: {
          type: Sequelize.DECIMAL(10, 2),
          allowNull: false,
          comment:
            'O valor do documento ou da parte do documento que ' +
            'servirá de comprovação',
        },
        AtualizadoEm: {
          type: Sequelize.DATE,
          allowNull: true,
        },
        CriadoEm: {
          type: Sequelize.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW,
          comment:
            'Este campo é importante para indicar a data que vai ' +
            'ter início para computar o auxílio',
        },
        DboIdDocumento: {
          type: Sequelize.INTEGER,
          comment: 'Referência ao documento digitalizado',
        },
      },
      { schema: 'auxilio_saude_v2' },
    );
  },

  down: async (queryInterface, Sequelize) => {
    return await queryInterface.dropTable('Comprovante');
  },
};

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    return await queryInterface.createTable(
      'Ressarcimento',
      {
        IdRessarcimento: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        Tipo: {
          type: Sequelize.ENUM('recorrente', 'nao-recorrente'),
          allowNull: false,
        },
        Descricao: {
          type: Sequelize.STRING,
          allowNull: false,
        },
        MesCompetencia: {
          type: Sequelize.INTEGER,
          allowNull: false,
          comment: 'A partir de/Em qual mês o ressarcimento valerá',
        },
        AnoCompetencia: {
          type: Sequelize.INTEGER,
          allowNull: false,
          comment: 'A partir de/Em qual ano o o ressarcimento valerá',
        },
        Valor: {
          type: Sequelize.DECIMAL(10, 2),
          allowNull: false,
          comment:
            'A atualização em valor gerará um novo ressarcimento ' +
            '(se ele for recorrente)',
        },
        Ativo: {
          type: Sequelize.BOOLEAN,
          allowNull: false,
        },
        MotivoNaoAtivo: {
          type: Sequelize.STRING,
          allowNull: true,
        },
        AtualizadoEm: {
          type: Sequelize.DATE,
          allowNull: true,
        },
        CriadoEm: {
          type: Sequelize.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW,
          comment:
            'Este campo é importante para indicar a data que vai ' +
            'ter início para computar o auxílio',
        },

        /** Foreign keys */
        IdComprovante: {
          type: Sequelize.INTEGER,
          references: {
            model: 'Comprovante',
            key: 'IdComprovante',
            schema: 'auxilio_saude_v2',
          },
        },
        DboIdVinculo: {
          type: Sequelize.INTEGER,
          references: {
            model: 'Vinculo',
            key: 'IdVinculo',
            schema: 'dbo',
          },
        },
      },
      { schema: 'auxilio_saude_v2' },
    );
  },

  down: async (queryInterface, Sequelize) => {
    return await queryInterface.dropTable('Ressarcimento');
  },
};

What do you expect to happen?

To create a foreign key in Ressarcimento.IdComprovante. If i comment

references: {
            model: 'Comprovante',
            key: 'IdComprovante',
            schema: 'auxilio_saude_v2',
          },

then, it works.

What is actually happening?

== 20181205154655-CreateAuxilioSaudeSchema: migrating =======
== 20181205154655-CreateAuxilioSaudeSchema: migrated (0.031s)

== 20181205154700-CreateAuxilioSaudeComprovanteTable: migrating =======
== 20181205154700-CreateAuxilioSaudeComprovanteTable: migrated (0.037s)

== 20181205155631-CreateAuxilioSaudeRessarcimentoTable: migrating =======

ERROR: Could not create constraint or index. See previous errors.

__Dialect:__ MSSQL
__Sequelize CLI version:__ latest
__Sequelize version:__ latest

[davegw]

Hey @victorschinaider - Experiencing a similar issue. Did you ever find a fix? Thanks!

[v1d3rm3]

No with sequelize. I've changed to TypeORM.

[fenrirx22]

same issue for me. The only way right now is to create two migrations. First to create the table, second to add the association.

[yogeshJB]

@victorschinaider
Hey, I am also facing the same issue, in below case, it works for me.
Hope this may be helpful to you,

references: {
    model: { tableName: 'Comprovante', schema: 'auxilio_saude_v2'},
    key: 'IdComprovante',
},

[DanielOverdevest]

@victorschinaider
Hey, I am also facing the same issue, in below case, it works for me.
Hope this may be helpful to you,

references: {
    model: { tableName: 'Comprovante', schema: 'auxilio_saude_v2'},
    key: 'IdComprovante',
},

@yogeshJB Hero of the day! Thanks!

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.