javascript - PostgreSQL entry to table violates foreign key constraint - Stack Overflow

admin管理员组

文章数量:1398780

I'm using sequelize to define my tables and their relations, and after that inserting some entries to the tables.

I've got three tables :

• BankAccounts : pk - accountNumber
• Businesses : pk - sequence id
• BusinessBankAccounts : pk- bankAccountAccountNumber & businessId

Relations :

BankAccount.belongsToMany(Business, {through: 'BusinessBankAccounts'});

Creating entries for BankAccount and Business:

Connection.models.bankAccount.create({accountNumber: 12345678, ...});
Connection.models.business.create({...});

These two entries are inserted correctly to the tables (using pgAdmin to inspect my DB), but as soon as im trying to insert an entry to connect these two :

Connection.models.BusinessBankAccounts.create({bankAccountAccountNumber: 12345678,
                                               businessId: 1});

An error pops up:

Unhandled rejection SequelizeForeignKeyConstraintError: insert or update on table 
"BusinessBankAccounts" violates foreign key constraint
"BusinessBankAccounts_bankAccountAccountNumber_fkey"

NOTE: Manually inserting the exact same entry using the pgAdmin tool no error pops up, and it is inserted!

Any help would be much appreciated!

Thanks in advance.

I'm using sequelize to define my tables and their relations, and after that inserting some entries to the tables.

I've got three tables :

• BankAccounts : pk - accountNumber
• Businesses : pk - sequence id
• BusinessBankAccounts : pk- bankAccountAccountNumber & businessId

Relations :

BankAccount.belongsToMany(Business, {through: 'BusinessBankAccounts'});

Creating entries for BankAccount and Business:

Connection.models.bankAccount.create({accountNumber: 12345678, ...});
Connection.models.business.create({...});

These two entries are inserted correctly to the tables (using pgAdmin to inspect my DB), but as soon as im trying to insert an entry to connect these two :

Connection.models.BusinessBankAccounts.create({bankAccountAccountNumber: 12345678,
                                               businessId: 1});

An error pops up:

Unhandled rejection SequelizeForeignKeyConstraintError: insert or update on table 
"BusinessBankAccounts" violates foreign key constraint
"BusinessBankAccounts_bankAccountAccountNumber_fkey"

NOTE: Manually inserting the exact same entry using the pgAdmin tool no error pops up, and it is inserted!

Any help would be much appreciated!

Thanks in advance.

• javascript
• postgresql
• foreign-keys
• relational-database
• sequelize.js

Share Improve this question Follow edited Oct 7, 2018 at 14:32 S-Man 23.8k88 gold badges4848 silver badges7474 bronze badges asked Sep 18, 2016 at 11:24 Kesem DavidKesem David 2,24533 gold badges2929 silver badges4848 bronze badges 3

• Commit between the two inserts. (pgadmin does an invisible automit for you) – wildplasser Commented Sep 18, 2016 at 11:27
• @wildplasser i see, any ideas how to mit in sequelize? – Kesem David Commented Sep 18, 2016 at 11:33
• @wildplasser Please do take a look at the answer i posted and upvote it if you think its well explained, im unable to accept my own answer for the next two days, Thanks a lot for your help. – Kesem David Commented Sep 18, 2016 at 12:12

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

Thanks to @wildplasser ment, I found on sequelize transaction docs how to force mit of some inserts.

Ended up doing as follows:

Connection.transaction((t)=> {
     return Promise.all([
       Connection.models.bankAccount.create({accountNumber: 12345678, ...}, 
                                            {transaction: t}),
       Connection.models.business.create({...},
                                         {transaction: t})
     ]).then((result)=> {
         Connection.models.BusinessBankAccounts.create({
             bankAccountAccountNumber: 12345678,
             businessId: 1
         });
     })
}

I was having the same issue myself with using Sequelize and Postgres together.

After doing some research, I found the 'deferrable' feature with Postgres that seemed to be the solution to my issue.

What you have will work, Kesem. My solution adds an option to Sequelize's transaction to enable Postgres's deferrable feature to allow me to insert as many rows as I want and deferring the foreign key constraints check until the transaction is about to mit rather than Postgres checking the constraint immediately after each insert statement. This allows me to run all of my create statements at one time rather than have to create a Promise chain as you have done.

var objectsToCreate = []
// all 3 of the objects you want to insert. 
objectsToCreate.push({accountNumber: 12345678, ...}) 
objectsToCreate.push({...})
objectsToCreate.push({bankAccountAccountNumber: 12345678, businessId: 1})

await sequelize.transaction({
  deferrable: sequelize.Deferrable.SET_DEFERRED // magic sauce.
}, (transaction) => {
  var promises = []
  objectsToCreate.forEach((objectToCreate) => {
    promises.push(Connection.models.BusinessBankAccounts.create(objectToCreate, {transaction:transaction}))
  })
  // Here, we are able to run all the queries at one time. No having to deal with a Promise().then().then().then() chain. 
  return Promise.all(promises)
})

本文标签： javascriptPostgreSQL entry to table violates foreign key constraintStack Overflow