Cannot add foreign key constraints

Question

I have checked this answer and made sure with both scenario are correct. But still having issue creating constraints:

Model:

user.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.INTEGER
    },
    username: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {});
  User.associate = function(models) {
    models.User.hasMany(models.Answer);
  };
  return User;
};

answer.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Answer = sequelize.define('Answer', {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.INTEGER
    },
    userId: {
      type: DataTypes.INTEGER,
      references: {
        model: 'Users',
        key: 'id'
      },
     allowNull: true
    },
    content: DataTypes.TEXT
  }, {});
  Answer.associate = function(models) {
    models.belongsTo(models.User)
  };
  return Answer;
};

Migration

...-create-user.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      username: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Users');
  }
};

...create-answer.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Answers', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      userId: {
        allowNull: true,
        type: Sequelize.INTEGER
      },
      content: {
        allowNull: false,
        type: Sequelize.TEXT
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Answers');
  }
};

What else might be the issue? Is it due to allowNull: true? But I am sure this should not be the case because I also tested using allowNull: false if it works, but it did not.

I have also tried model: User and .INTEGER(11) exactly but nothing work. I cannot see foreign key constraints on PHPMyAdmin.

Anatoly · Accepted Answer · 2020-05-12T17:10:42.130

1

You should add

userId: {
      type: Sequelize.INTEGER,
      references: {
        model: 'Users',
        key: 'id'
      },
     allowNull: true
    }

in create-answer.js

instead of

userId: {
        allowNull: true,
        type: Sequelize.INTEGER
      },

edited May 12 '20 at 17:10

answered May 12 '20 at 15:32

Anatoly

20,799
3
28
42

Try references: { model: { tableName: 'Users'}, key: 'id' } – Anatoly May 12 '20 at 15:49
Please update create-answer.js in your question to a current version with `references` so I can look at it – Anatoly May 12 '20 at 15:54
At the moment does a primary key data type exactly the same as a foreign key data type? – Anatoly May 12 '20 at 16:04
"Foreign key constraint is incorrectly formed" - is it from DB or sequelize itself? If it's from DB can you look at SQL? – Anatoly May 12 '20 at 16:10
updated answer. Can you try this? Just write down a schema where a Users table must be placed – Anatoly May 12 '20 at 16:21
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/213718/discussion-between-anatoly-and-gacat). – Anatoly May 12 '20 at 16:41
Rollback to what version? – Anatoly May 12 '20 at 17:09

Cannot add foreign key constraints

Model:

Migration

1 Answers1