Sling Academy
Home/Node.js/Sequelize.js Error: Foreign key constraint is incorrectly formed [fixed]

Sequelize.js Error: Foreign key constraint is incorrectly formed [fixed]

Last updated: December 29, 2023

Understanding the Error

The ‘Foreign key constraint is incorrectly formed’ error typically indicates that there is a mismatch in the configuration between the two tables that are being linked by the foreign key. This discrepancy could be due to several reasons such as a difference in data types or character sets, the referenced table not existing by the time the foreign key is being created, or the Sequelize model not reflecting the true state of your database schema. Understanding the exact cause of the issue is key to resolving it.

Slutions

Synchronous Table Creation

One common fix involves ensuring that your Sequelize models are synchronized with your database in the correct order. Since foreign key constraints require the reference table to be available beforehand, the referenced table must be created before any referencing table with foreign keys is attempted to be synchronised. This can be done using the ‘sequelize.sync’ method and properly structuring your code to ensure tables are synced sequentially rather than concurrently.

Consistent Data Definitions

Another crucial step is to check for consistency in the data types of the primary key in the referenced table and the foreign key in the referencing table. Even a minor difference like an unsigned integer in one table and a signed integer in another can throw this error. It’s important that both keys are defined with the same specifications in terms of types and other constraints.

Example Code

Let’s examine an example to get a better understanding of how to avoid the ‘foreign key constraint is incorrectly formed’ error:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mysql'
});

// Define the User model
const User = sequelize.define('User', {
  id: {
    type: Sequelize.INTEGER.UNSIGNED,
    autoIncrement: true,
    primaryKey: true
  },
  name: Sequelize.STRING
});

// Define the Post model
const Post = sequelize.define('Post', {
  title: Sequelize.STRING,
  content: Sequelize.TEXT,
  userId: {
    type: Sequelize.INTEGER.UNSIGNED,
    references: {
      model: User,
      key: 'id'
    }
  }
});

// Sync models
sequelize.sync({ force: true })
  .then(() => {
    console.log('Tables have been created');
  }).catch((error) => {
    console.error('Unable to create tables:', error);
});

In the above example, the User model is defined with an ‘id’ that is an unsigned integer, meant to serve as the primary key. The Post model references User with a foreign key that is also an unsigned integer, thus maintaining type consistency.

Synchronization of the models is being forced by passing { force: true } which will drop tables if they exist and recreate them; this should be used cautiously as it will lead to data loss. In a production environment, table creation should use migrations instead of sync for safer database change management.

Charset Consistency

Lastly, setting explicit character sets and collations for your tables and ensuring they match can also resolve ‘incorrectly formed’ errors. This requires knowledge of the desired character sets and ensuring your databases, tables, and Sequelize models all uniformly use those settings.

Next Article: Using afterCreate and afterUpdate hooks in Sequelize.js

Previous Article: How to Disable Logging SQL in Sequelize.js

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates