Search code examples
oraclesql-updateunique-constraintsysdate

Not able to perform UPDATE query with sysdate - ORACLE

I am trying to run the following, fairly simple, update statement in ORACLE.

UPDATE PROJECT_BUG_SNAPSHOTS
SET   SNAPSHOT_DATESTAMP = sysdate, 
      SNAPSHOT_TYPE      = P_SNAPSHOT_TYPE
      WHERE  PROJECT_ID  = P_PROJECT_ID
      AND    BUG_NO      = P_BUG_NO
      AND    BUG_STATUS  = P_BUG_STATUS;

It complains of unique constraint violation.

The PK comprises of PROJECT_ID,BUG_NO,SNAPSHOT_DATESTAMP,SNAPSHOT_TYPE.

The table structure is

PROJECT_ID          NUMBER
SNAPSHOT_DATESTAMP  DATE
SNAPSHOT_TYPE       VARCHAR2(20 BYTE)
BUG_NO              NUMBER
BUG_STATUS          VARCHAR2(100 BYTE)

This is quite weird as sysdate should be different with each run and it should never hit the "unique constraint violation" error.

Solution

  • The primary key is a combination of PROJECT_ID, BUG_NO, SNAPSHOT_DATESTAMP, and SNAPSHOT_TYPE. This means you allow (and probably have!) several rows with the same project id, bug number and snapshot type, but from different dates. Your update statement, will attempt to set all the snapshot dates of a given project, bug number and status to the same date (the current date), thus breaking the uniqueness and failing due to a constraint violation.