Question 272518 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Unique Indexes not working for Sequelize Model

I have a unique index for a model that’s not working. Here is my model

const Script = sequelize.define('Script', {
  name: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  deletedAt: {
    type: DataTypes.DATE,
    allowNull: true
  },
}, {
  indexes: [
    {
      unique: true,
      fields: ['userId', 'name', 'deletedAt']
    }
  ],
  timestamps: true,
  paranoid: true
});

User.Script = User.hasMany(Script, {
  foreignKey: {
    allowNull: false,
    name: 'userId'
  },
  onDelete: 'CASCADE',
});

Script.User = Script.belongsTo(User, { foreignKey: 'userId' });

I checked the database and the index exists. So when I create 2 scripts with the same userId and name, it doesn’t throw an error as expected. Instead I’m able to create 2 records with the same values for all fields in the index. Both of these records have deletedAt: null. Maybe it has something to do with the the field deletedAt but I’m not sure why. When I created the index with just ['userId', 'name'] that seemed to work. Any one have any ideas as to why the unique index with deletedAt doesn’t work?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After understanding that postgres doesn't considered two null values as equal (Thank you Frank Heikens), I can rewrite my index this way so that I can achieve the unique constraint I desire.

       indexes: [
    {
      unique: true,
      fields: ['userId', 'name'],
      where: { deletedAt: null }
    }
  ]

    This way the combination of userId and name will be unique only among rows where deletedAt is null.


  2. 0

    From the manual:

    In general, a unique constraint is violated if there is more than one
    row in the table where the values of all of the columns included in
    the constraint are equal. By default, two null values are not
    considered equal in this comparison. That means even in the presence
    of a unique constraint it is possible to store duplicate rows that
    contain a null value in at least one of the constrained columns. This
    behavior can be changed by adding the clause NULLS NOT DISTINCT, like

    CREATE TABLE products (
    product_no integer UNIQUE NULLS NOT DISTINCT,
    name text,
    price numeric );

    So for NULL’s you have to tell the database what to consider unique.

    No idea how to do that in sequelize.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search