CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Hello World
Hello World

Reputation: 2907

check for unique if only value is provided sequelize

I have a users table which consists few columns including a mobile number and email address column.

I want to make both mobile and email column to be unique.

I is working perfectly until some users prefers to not provide email address.

When the email column is been empty it shows the unique key error message.

Is there any way to make a column unique only if the value is provided for the column in sequelize?

Here is a piece of code of my users schema:

const schema = sequelize.define("users", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: DataTypes.STRING,
    },
    email: {
        type: DataTypes.STRING,
        unique: {
            msg: "Email address is already registered"
        },
    },
    mobile: {
        type: DataTypes.STRING,
        unique: {
            msg: "Mobile number is already registered"
        },
    },

});

Upvotes: 0

Views: 685

Answers (1)

Ayzrian
Ayzrian

Reputation: 2465

Well in the way you are implementing it is impossible to do so. You need to understand that when you write unique it means that your Database will create an index for that field. So once you have empty string in, every next empty string will cause the error because it is not unique.

The short way to handle this is

  1. Remove a way to specify a NULL value for your fields. It is actually work easily without NULL values in your database.
  2. Force users to input email and mobile

If still want to have that custom logic you need to define a trigger on BEFORE INSERT that will perform custom validation of row.

Upvotes: 2

Related Questions