CODEWITHSUNDEEP
sqlpostgresqlduplicates
hdx
hdx

Reputation: 4558

Why would I get a duplicate key error when updating a row?

I'm using postgres and I'm getting the duplicate key error when updating a row:

cursor.execute("UPDATE jiveuser SET userenabled = 0 WHERE userid = %s" % str(userId))
psycopg2.IntegrityError: duplicate key value violates unique constraint "jiveuser_pk"

I don't understand how updating a row can cause this error... any help will be much appreciated.

Upvotes: 2

Views: 4464

Answers (4)

stu
stu

Reputation: 8805

Triggers. Find out what your DBA did when you weren't looking, that triggers fire and do all sorts of random things you had no idea were going on, and IT generated the duplicate error and caused your transaction to fail.

Upvotes: 1

John Douthat
John Douthat

Reputation: 41189

You would need to know the components of the primary key to know. Try running \d jiveuser_pk from the command-line. I'm guessing that the PK on that table is (userid, userenabled) or that there is some trigger that is running after the update.

Upvotes: 0

dreadwail
dreadwail

Reputation: 15410

Could happen if userenabled is part of a key and setting it to 0 collides with another key with the same value.

It's also possible there is a trigger in play here, though less likely than a simple key collision.

I suppose to answer fully we need to see what the primary key is for that table.

Upvotes: 0

Nathan Ernst
Nathan Ernst

Reputation: 4590

The error would seem to indicate that the userenabled column is participating in the jiveuser_pk primary key. My guess is that both userid and userenabled participate in the primary key, and that you've already a userid where userenabled is equal to zero.

Upvotes: 3

Related Questions