Did some more digging. user_id field is set to auto_increment in the database, but wasn't configured that way in the model.

Error was gone after updating the model, but I still suggest the idea it should rollback when it throws an Unique Constraint Exception.

I am facing the same issue. Creation succeds but also gives a SequelizeUniqueConstraintError.
Anybody here who has found a solution ?

If it is due to the id field being set to auto increment, setting the field's auto increment property on your model fixes it.

Example:
tableID: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true, field: 'table_id' }

@okankaya thanks ,you save me !

I am confused by this functionality because if the query finds a record under the given parameters shouldn't it return that value and skip the create part? Otherwise this is a findAndCreate method which doesn't make sense. I am using this Facebook OAuth and searching for their FB id so the increment solution mentioned is not available to me.

Expected results: Creates row in database with user data when logging into app for the first time, on subsequent login attempts it retrieves this existing data and does not try to create new user instance.

Notes: Using Postgres 9.5.4 & Sequelize 3.30.2

User.findOrCreate({ where: { auth_provider: "fb", facebook_id: userID } }) .spread((user, created) => { const user = userData.dataValues; console.log(user) // returns expected data if there is no unique constraint error console.log(created) }) .catch(err => console.log(err)); // throws SQL error
Am I misunderstanding how to implement this method?
I am handling the error in the catch but if the name of the function is accurate then no error should be thrown. Can you please explain where my thought process or code is going incorrect?

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

Hello!
I am facing a similar issue. I have a User model with id as primary key which is set to auto increment. This model also has a unique key which is "username". I am using the findOrCreate method to create or lookup a user based on the "username" field. My code creates a record if it does not exist but throws a UniqueConstraintError (ER_DUP_ENTRY) --> SequelizeUniqueContraintError on the "username" field when I try to find this record. Basically the findOrCreate tries to create a record for the second time instead of finding it.

I am also experiencing the same issue as @nandeshnair:
Basically the findOrCreate tries to create a record for the second time instead of finding it.

Error msg:

{
  "name": "SequelizeUniqueConstraintError",
  "errors": [
    {
      "message": "uniqueApprovalActionNeedAllTheseFields must be unique",
      "type": "unique violation",
      "path": "uniqueApprovalActionNeedAllTheseFields",
      "value": "16, req, approve, 6541, objcodeapproval, objcode, 5200, 1.0957, 0"
    }
  ],
  "fields": {
    "uniqueApprovalActionNeedAllTheseFields": "16, req, approve, 6541, objcodeapproval, objcode, 5200, 1.0957, 0"
  },
  "parent": {
    "message": "Violation of UNIQUE KEY constraint 'uniqueApprovalActionNeedAllTheseFields'. Cannot insert duplicate key in object 'dbo.approval_action'. The duplicate key value is (16, req, approve, 6541, objcodeapproval, objcode, 5200, 1.0957, 0).",
    "code": "EREQUEST",
    "number": 2627,
    "state": 1,
    "class": 14,
    "serverName": "KALEIKALANA81E6",
    "procName": "",
    "lineNumber": 1,
    "sql": "INSERT INTO [approval_action] ([patientType],[patientValue],[priceOrAmount],[itemId],[outcomeType],[documentType],[documentId],[approverUserAccountId],[action],[selfApproved],[createdAt],[updatedAt]) OUTPUT INSERTED.* VALUES (N'objcode',N'5200',1.09574972,N'0',N'objcodeapproval',N'req',6541,16,N'approve',1,N'2018-03-17 04:58:12.000 +00:00',N'2018-03-17 04:58:12.000 +00:00');"
  },
  "original": {
    "message": "Violation of UNIQUE KEY constraint 'uniqueApprovalActionNeedAllTheseFields'. Cannot insert duplicate key in object 'dbo.approval_action'. The duplicate key value is (16, req, approve, 6541, objcodeapproval, objcode, 5200, 1.0957, 0).",
    "code": "EREQUEST",
    "number": 2627,
    "state": 1,
    "class": 14,
    "serverName": "KALEIKALANA81E6",
    "procName": "",
    "lineNumber": 1,
    "sql": "INSERT INTO [approval_action] ([patientType],[patientValue],[priceOrAmount],[itemId],[outcomeType],[documentType],[documentId],[approverUserAccountId],[action],[selfApproved],[createdAt],[updatedAt]) OUTPUT INSERTED.* VALUES (N'objcode',N'5200',1.09574972,N'0',N'objcodeapproval',N'req',6541,16,N'approve',1,N'2018-03-17 04:58:12.000 +00:00',N'2018-03-17 04:58:12.000 +00:00');"
  },
  "sql": "INSERT INTO [approval_action] ([patientType],[patientValue],[priceOrAmount],[itemId],[outcomeType],[documentType],[documentId],[approverUserAccountId],[action],[selfApproved],[createdAt],[updatedAt]) OUTPUT INSERTED.* VALUES (N'objcode',N'5200',1.09574972,N'0',N'objcodeapproval',N'req',6541,16,N'approve',1,N'2018-03-17 04:58:12.000 +00:00',N'2018-03-17 04:58:12.000 +00:00');"
}

• db (sql server) unique constraint attached
• schema file attached as well

Thanks!

@charlieargue Hi Karl!

Can you please share your findOrCreate method call as well?
I solved this issue by specifying the unique key in the "where" clause and all the other attributes in the "defaults" clause

An example from the official documentation:
http://docs.sequelizejs.com/manual/tutorial/models-usage.html

User
  .findOrCreate({where: {username: 'sdepold'}, defaults: {job: 'Technical Lead JavaScript'}})
  .spread((user, created) => {
    console.log(user.get({
      plain: true
    }))
    console.log(created)

Model.upsert({
title:your title,
desc:your description,
location:your locations
}).then(function (test) {
if(test){
res.status(200);
res.send("Successfully stored");
}else{
res.status(200);
res.send("Successfully inserted");
}
})

Use upser to prevnt this case