Skip to content

Conversation

@gtamasi
Copy link

@gtamasi gtamasi commented Mar 11, 2025

Pull Request Checklist

  • Have you added new tests to prevent regressions?
  • If a documentation update is necessary, have you opened a PR to the documentation repository?
  • Did you update the typescript typings accordingly (if applicable)?
  • Does the description below contain a link to an existing issue (Closes #[issue]) or a description of the issue you are solving?
  • Does the name of your PR follow our conventions?

Description of Changes

Currently, Sequelize primarily supports single-column foreign keys. However, many database schemas utilize composite foreign keys to enforce data integrity and model complex relationships more accurately. This feature addresses this limitation and provides greater flexibility in defining database mappings.

Issue: #311

Changes Introduced:

  • foreignKey option in belongsTo, hasOne, and hasMany associations contains an extra field called keys which holds the keys used for composite keys. This allows specifying multiple columns that constitute the foreign key in the source model.
  • The keys can be defined by either with a string which is attribute of the model or by adding sourceKey and targetKey attribute. The logic is the same as if you use the targetKey attribute in case of single column key.
  • Updated association logic to correctly handle composite foreign key constraints** during data retrieval, creation, update, and deletion operations.

With this design, the aim was the backwards compatibility so this PR contains no breaking changes.

Example Usage:

// Define the target model with a composite primary key
   const User = this.sequelize.define('User', {
              userId: {
                type: DataTypes.INTEGER,
                primaryKey: true,
              },
              tenantId: {
                type: DataTypes.INTEGER,
                primaryKey: true,
              },
              username: DataTypes.STRING,
            });
            const Address = this.sequelize.define(
              'Address',
              {
                addressId: {
                  type: DataTypes.INTEGER,
                  primaryKey: true,
                  autoIncrement: true,
                },
                zipCode: DataTypes.STRING,
              },
            );
   Address.belongsTo(User, { foreignKey: { keys: ['userId', 'tenantId'] } });



// Define Belongs-to-many

      this.User = this.sequelize.define('User', {
        userId: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        tenantId: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: false,
        },
        name: DataTypes.STRING,
      });
      this.Project = this.sequelize.define('Project', {
        projectId: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        tenantId: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: false,
        },
        name: DataTypes.STRING,
      });

      this.User.belongsToMany(this.Project, {
        through: 'user_projects',
        as: 'Projects',
        inverse: {
          as: 'Users',
        },
        foreignKey: {
          keys: ['userId', 'tenantId'],
        },
        otherKey: {
          keys: ['projectId', 'tenantId'],
        },
      });
    });

Notes

In SQLite3, you only get autoincrement behavior when only one integer column is the primary key. Therefore, you cannot define a composite primary key when one of the column defined as AUTOINCREMENT.
You can get a similar result by defining a column as the only primary key, but then adding an additional unique constraint on columns.

List of Breaking Changes

wd-alejandroescobedogarcia and others added 30 commits June 17, 2024 14:24
This is the "known good" state of the findbypk-composite-support branch
* commit '0717a42ad':
  change misused property
  check if model has pk first
  prettier changes
  code review changes
  prettier changes
  add composite key findByPk support via object
…patch' into sequelize-core-papandreou9

* feature/composite-primary-key-support-and-associations-patch:
  Fix issue with includes and hasmany
  use ? to check if it is safe to push to array in hasmany
  add missing tests
  cleanup models file
  add constraintName property
  update types
  consolidate changes from other branches
(cherry picked from commit df19318)
(cherry picked from commit 6eb101e)
(cherry picked from commit c48086b)
(cherry picked from commit 00fad6a)
* main:
  fix(core): Adjust model validator types yet again (sequelize#17689)
… gtamasi/sequelize-composite-foreign-key-support
@gtamasi gtamasi marked this pull request as ready for review April 8, 2025 20:06
@gtamasi gtamasi requested a review from a team as a code owner April 8, 2025 20:06
@gtamasi gtamasi requested review from ephys and sdepold April 8, 2025 20:06
@sequelize-bot sequelize-bot bot added the conflicted This PR has merge conflicts and will not be present in the list of PRs to review label Apr 8, 2025
@sequelize-bot sequelize-bot bot removed the conflicted This PR has merge conflicts and will not be present in the list of PRs to review label Apr 8, 2025
/**
* The pairs of the foreign key attributes used for composite foreign keys.
*/
keys?: ForeignKey[] | Array<CompositeForeignKeysOptions<ForeignKey, SourceKey>>;
Copy link
Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

We consciously went down this road to make this change backwards-compatible. We discussed the API design choice proposed in #311, but realized that by having foreignKeys as an array would make it impossible to define constraints and cascading for the composite key itself.

@gtamasi gtamasi changed the title feat(core): Add composite foreign key support WIP: Add composite foreign key support Apr 9, 2025
@gtamasi gtamasi changed the title WIP: Add composite foreign key support feat(core): Add composite foreign key support for associations Apr 9, 2025
@WikiRik WikiRik self-requested a review May 1, 2025 21:04
@sequelize-bot sequelize-bot bot added the conflicted This PR has merge conflicts and will not be present in the list of PRs to review label Oct 25, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

conflicted This PR has merge conflicts and will not be present in the list of PRs to review

Projects

None yet

Development

Successfully merging this pull request may close these issues.

4 participants