Adding foreign key to a table using Sequelize

Sequelize can add a foreign key constraint to the desired SQL table by using one of the following association methods:

• hasOne()
• belongsTo()

Both hasOne and belongsTo() are Sequelize association methods that enable you to create a relation between two Sequelize models.

The model from which you call the method is also known as the source model. And the model that’s passed as an argument to the method is the target model.

For example, suppose you have two tables in your database with the following details:

• The Users table hold records of users in your application
• The Invoices table stores records of invoices created by your users
• One Invoices row has one userId as its foreign key constraint

Relating to the above details, the model for the two tables can be defined as follows:

const User = sequelize.define(
  "User",
  { firstName: Sequelize.STRING },
  { timestamps: false }
);

const Invoice = sequelize.define(
  "Invoice",
  { amount: Sequelize.INTEGER },
  { timestamps: false }
);

Please note that by default, a Sequelize model will look for the SQL table that has the plural form of the model name. The User model will look for the Users table, and so on.

Now you can call the association methods from one of the models.

The hasOne() method generates a foreign key constraint on the target model’s table. You can call the method from the User model as follows:

User.hasOne(Invoice); // foreign key added to the Invoices table

Or you can call the belongsTo() method from the Invoice model:

Invoice.belongsTo(User); // foreign key added to the Invoices table

When you add a foreign key constrain using Sequelize, it will also add one column that will store the foreign key value for the table.

The default name of the foreign key added by Sequelize will be the model name and the primary key attribute of the model.

So in this example, it will be the User + Id = UserId.

When you call the sync() method to create the tables using Sequelize, here’s the generated CREATE TABLE statements:

CREATE TABLE IF NOT EXISTS `Users` (
  `id` integer NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255),
  PRIMARY KEY (`id`)) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Invoices` (
  `id` integer NOT NULL AUTO_INCREMENT,
  `amount` integer,
  `UserId` integer,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`UserId`)
    REFERENCES `Users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE) ENGINE = InnoDB;

Sequelize association methods also accept an options object that you can use to configure the details of the association.

For example, you can change the foreign key name on the table by adding the foreignKey property:

User.hasOne(Invoice, {
  foreignKey: "invoice_creator", // UserId -> invoice_creator
});

You can also change the column used as a reference for the foreign key from the default primary key.

For the hasOne() method, define the sourceKey property:

User.hasOne(Invoice, {
  foreignKey: "invoice_creator",
  sourceKey: "firstName",
});

For the belongsTo() method, use the targetKey property:

Invoice.belongsTo(User, {
  foreignKey: "invoice_creator",
  targetKey: "firstName",
});

You can also change other configurations for the foreign key constraints, such as the onDelete and onUpdate constraints.

You can even turn the constraints off by adding the constraints: false property to the option:

Invoice.belongsTo(User, {
  foreignKey: "invoice_creator",
  targetKey: "firstName",
  constraints: false,
});

Finally, you can change the constraint name through the uniqueKey property:

Invoice.belongsTo(User, {
  uniqueKey: "user_invoice_fk_constraint" // default invoices_ibfk_1
});

The default name for foreign key constraints should be [target]_ibfk_[number], but you can change it if you like.

You can view the documentation for the association methods here:

• Sequelize hasOne()
• Sequelize belongsTo()

Now you’ve learned how to add foreign key constraints to SQL tables using Sequelize. Nice work! 😉