CODEWITHSUNDEEP
node.jsormsequelize.js
Diego Vidal
Diego Vidal

Reputation: 163

Sequelize Composite Foreign Key

I have a database with the following tables:

CREATE TABLE IF NOT EXISTS `app_user` (
  `user_id` INT NOT NULL,
  `user_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `user_folder` (
  `user_id` INT NOT NULL,
  `document_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `document_id`),
  CONSTRAINT `fk_user_document_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `zinc`.`app_user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `folder_content` (
  `user_id` INT NOT NULL,
  `document_id` INT NOT NULL,
  `content_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `document_id`, `content_id`),
  CONSTRAINT `fk_folder_content_folder`
    FOREIGN KEY (`user_id` , `document_id`)
    REFERENCES `zinc`.`user_folder` (`user_id` , `document_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

I need to create a Sequelize model to represent it. The only problem I have is with the relation folder_content and user_folder because of the composite key.

How can I create this sequelize model?

This is what I have so far:

var AppUser = sequelize.define('app_user', 
{userId: {type: Sequelize.INTEGER, primaryKey: true, field: 'user_id'}, ... } );

var UserFolder = sequelize.define('user_folder', 
{userId: {type: Sequelize.INTEGER, primaryKey: true, field: 'user_id'}, 
documentId: {type: Sequelize.INTEGER, primaryKey: true, field: 'document_id'}... });

var FolderContent = sequelize.define('folder_content', {
userId: {type: Sequelize.INTEGER, primaryKey: true, field: 'user_id'}, 
documentId: {type: Sequelize.INTEGER, primaryKey: true, field: 'document_id'}, 
contentId: {type: Sequelize.INTEGER, primaryKey: true, field: 'content_id'}... });


UserFolder.hasMany(FolderContent);
FolderContent.belongsTo(UserFolder, {foreingKey: !! });// <- PROBLEM

Upvotes: 15

Views: 11919

Answers (3)

Bruno
Bruno

Reputation: 454

The solution I came up with was just merging the columns in a view. Then use the view as a replacement for the actual table in sequelize

In you case:

CREATE VIEW user_folder_composite AS SELECT *,CONCAT(`user_id`, `:`,`document_id`) FROM user_folder;

CREATE VIEW folder_content_composite AS SELECT *, CONCAT(`user_id`,`:`, `document_id`,`:` ,`content_id`) FROM folder_content;

Now just you these in your model instead of the original table.

Upvotes: 1

friartuck
friartuck

Reputation: 3121

does sequelize still not support composite foreign keys? i would like to have a table with 2 foreign keys as the composite primary key. i would rather have it this way than have a surrogate key as the primary key with a unique constraint over the 2 foreign key fields.

thanks

Upvotes: 0

Faris
Faris

Reputation: 241

Now Sequelize doesn't support composite foreign keys. This creates several problems.

  1. When Sequelize creates a table, the table definition does not have a composite FK.
    To solve this problem I use the afterSync hook on the model and a function that adds a FK to the table if it does not exist. Example code.
  2. When I use the findAll method with include such model, I use the include[].on option of the findAll method. Or if you don't use as many joins as I do, you can use scope when creating an association (see).

Upvotes: 0

Related Questions