CODEWITHSUNDEEP
sequelize.jscomposite-keyunique-key
Lazarus Rising
Lazarus Rising

Reputation: 2685

Sequelize, many unique composite keys using same field

If I am designing a table containing the following fields column_one, column_two, column_three, and I want the combination (column_one:column_two) to be unique, I would do the following:

column_one: { type: DataTypes.INTEGER, unique: 'composite_one'},
column_one: { type: DataTypes.INTEGER, unique: 'composite_one'},
column_three: { type: DataTypes.INTEGER}

What if I need two unique combinations, both including one of the fields? Ex: (column_one:column_two) and (column_one:column_three) must both be unique, though (column_two:column_three) can be repeated

In theory it would be something like:

column_one: { type: DataTypes.INTEGER, unique: 'composite_one', unique: 'composite_two'},
column_one: { type: DataTypes.INTEGER, unique: 'composite_one'},
column_three: { type: DataTypes.INTEGER, unique: 'composite_two'}

This is not a join table, and I do not want to use any raw queries.

Also, column_one must create unique combinations with 2 and 3, but it does not mean that column one, two and three must create a single unique combination.

In case the explanation is not clear, I am giving an example:

column_one ----- column_two ---- column_three
-11---------------12----------------13-------
-11---------------12----------------14-------
-18---------------12----------------15-------

is not a valid set of data, because there are two rows with (column_one: column_two) = (11:12)

But

column_one ----- column_two ---- column_three
-11---------------12----------------13-------
-11---------------17----------------12-------
-18---------------12----------------15-------
-11---------------12----------------15-------

is a valid set of data

Upvotes: 2

Views: 3764

Answers (2)

Lazarus Rising
Lazarus Rising

Reputation: 2685

I managed to solve this using indexes. This is a link to the full documentation from sequelize docs. As for the implementation, in the model I added the following code after the field:

indexes: [
    { fields: ['column_one', 'column_two'], unique: true },
    { fields: ['column_one', 'column_three'], unique: true }
]

A similar approach is used if I would like to migrate the changes of the model to an existing database:

queryInterface.addIndex('epg_data', ['column_one', 'column_two'], {unique: true})
queryInterface.addIndex('epg_data', ['column_one', 'column_three'], {unique: true})

Upvotes: 2

Shivam
Shivam

Reputation: 3642

Over your migrations, you can use sequelize addConstraint method

From the Docs

 queryInterface.addConstraint('Users', ['email', 'name'], { type: 'unique', name: 'custom_unique_constraint_name' });

Upvotes: 1

Related Questions