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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.