Sequelize Unique Constraint Across Two Tables

Question

I have three tables: survey, survey_owners (join table), users. Surveys naturally have titles and are owned by users. A user can own multiple surveys and a survey can be owned by multiple users (many-to-many relationship).

I have the unique constraint setup on the survey_owners table so there are no duplicates, but now need to figure out how to enforce a unique constraint to address the following: A user should not be able to own multiple surveys with the same title.

That being said, a unique constraint CANNOT be placed on the 'title' column of the survey table because the uniqueness should be only be applied if a user already owns a survey with an identical name.

Any ideas how to implement this in the Sequelize migration and/or model(s)?

Current migration file for survey_owners

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('survey_owners', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      surveys_id: {
        type: Sequelize.INTEGER,
        onDelete: 'CASCADE',
        references: {
          model: 'surveys',
          key: 'id'
        }
      },
      users_id: {
        type: Sequelize.INTEGER,
        references: {
            model: 'users',
            key: 'id'
        }
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        field: "created_at"
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        field: "updated_at"
      }
    })
    .then(() => {
      return queryInterface.addConstraint('survey_owners', ['surveys_id', 'users_id'], {
          type: 'unique',
          name: 'survey_owners'
      });
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('survey_owners');
  }
};

You can't have a unique constraint across multiple tables. One way to do this is to add Title column in your joining table, and then have a unique constraint for `ownerId` and `title` (along with your existing unique constraint). — Ashwani Agarwal, Apr 21 '19 at 11:45
Thanks for the idea @AshwaniAgarwal but unfortunately this too is not possible as the `title` field is not uniquely indexed on the Survey table. When trying to add it to the join table, Sequelize throws the following error: `there is no unique constraint matching given keys for referenced table "survey_owners"` — James, Apr 22 '19 at 12:18

score 0 · Answer 1 · answered May 07 '19 at 23:48

0

Unfortunately I could not find a way inside Sequelize to handle this constraint so I am handling the logic on the submit action and checking in a JS method. Not the best way but had to move on and this is working.

answered May 07 '19 at 23:48

James

429
1
8
17

it's definitely worth noting that doing the logic in a JS method will not prevent duplicates in all cases unless if you are requiring a table lock before inserting. – mistahenry Dec 14 '20 at 20:48

Sequelize Unique Constraint Across Two Tables

1 Answers1