CODEWITHSUNDEEP
mysqlnode.jssequelize.js
jamesdeath123
jamesdeath123

Reputation: 4606

Sequelize foreign key of uuid gives error 1215

given the following migration snippet:

const uuid = require('uuid');
queryInterface.createTable('listings', {
        id: {
          type: Sequelize.UUID,
          defaultValue: () => uuid.v4(),
          primaryKey: true,
          allowNull: false,
          isUnique: true,
        },
});

and another one: 

queryInterface.createTable('listing_detail_info', {
        id: {
          type: Sequelize.INTEGER(11),
          primaryKey: true,
          autoIncrement: true,
          allowNull: false,
          isUnique: true,
        },
        listingId: {
          type: Sequelize.UUID,
          isUnique: true,
          allowNull: false,
          field: 'listing_id',
          references: { model: 'listings', key: 'id' },
        },
});

the following error happened:

 code: 'ER_CANNOT_ADD_FOREIGN',
     errno: 1215,
     sqlState: 'HY000',
     sqlMessage: 'Cannot add foreign key constraint',
     sql:
      'CREATE TABLE IF NOT EXISTS `listing_detail_info` (`id` INTEGER(11) NOT NULL auto_increment , `listing_id` CHAR(36) BINARY NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`)) ENGINE=InnoDB;' },

I am pretty sure they are the same data type (both Sequleize.UUID), and the previous table has been successfully created, but no idea why this still happens...

Upvotes: 0

Views: 774

Answers (2)

Adam Abd
Adam Abd

Reputation: 1

This codes below works for me. Btw this is what I'm using

  • "express": "^4.18.1"
  • "sequelize": "^6.21.3"
  • "mysql2": "^2.3.3",

for listings i just change defaultValue to Sequelize.UUIDV4

'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('listings', {
      id: {
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
        primaryKey: true,
        allowNull: false,
        isUnique: true,
      },
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('listings');
  }
};

for this part is same

'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('listing_detail_info', {
      id: {
        type: Sequelize.INTEGER(11),
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
        isUnique: true,
      },
      listingId: {
        type: Sequelize.UUID,
        isUnique: true,
        allowNull: false,
        field: 'listing_id',
        references: { model: 'listings', key: 'id' },
      },
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('listing_detail_info');
  }
};

Upvotes: 0

Vishal
Vishal

Reputation: 1290

just try this 

queryInterface.createTable('listing_detail_info', {
        id: {
          type: Sequelize.INTEGER(11),
          primaryKey: true,
          autoIncrement: true,
          allowNull: false,
          isUnique: true,
        },
        listingId: {
          type: Sequelize.UUID,
          isUnique: true,
          allowNull: false,
        },
});

if given solution work then there is problem to add foreign key constraint.

Upvotes: 1

Related Questions