Alter Column from a date to accept systimestamp

Question

At the current time I have a trigger that updates a date field with sysdate, unfortunately this can sometimes not be unique if an update occurs within the same second. Is it possible to alter a column to use systimestamp so that it using milliseconds and will always be unique.

The triger looks like this:

BEGIN
IF INSERTING or :old.STATUS_FLAG <> :new.STATUS_FLAG THEN
INSERT INTO T_SCHEME_STATUS_HISTORY
  (SCHEME_ID, STATUS_FLAG, DATE_STATUS_CHANGED, AUDIT_CREDENTIALS, AUDIT_DTM)
VALUES
  (:new.SCHEME_ID, :new.STATUS_FLAG, sysdate, :new.AUDIT_CREDENTIALS, SYSDATE);
END IF;  
END;

I want to change the DATE_STATUS CHANGED to use systimestamp so it is always unqiue but the column it is updating is of type DATE(7) so it won't fit.

Is there a way of altering the table to accept systimestamp? Let me know if you need any more information.

Using a DATE or a TIMESTAMP field as a primary or unique key is not a good idea. Just because you decrease the chances of collisions by using a TIMESTAMP, you don't remove the likelihood that there won't be any. — Boneist, Aug 13 '15 at 10:17
the issue i have is that this code is legacy and I am trying to find a solution to the problem. I would have loved to design the code a completely different way but now I have to deal with what's there. — Tobias, Aug 13 '15 at 10:26
You can't add another column, add a sequence which you use to populate the new column, and then switch the unique constraint to reference the sequence column? You could even add a trigger to do the population of the new column, if you're not able to amend the code to handle the insert. — Boneist, Aug 13 '15 at 10:28

score 1 · Accepted Answer · answered Aug 13 '15 at 12:10

@Boneist's remarks about not using time values as keys are spot on. HOWEVER, if you're absolutely bound and determined to do this - yeah, sure, it can be done:

ALTER TABLE WHATEVER
  MODIFY (SOME_DATE_FIELD TIMESTAMP(6));

This changes the data type to a TIMESTAMP(6), which is accurate down to 1/1,000,000 of a second. Hopefully this will satisfy your requirement, but really - date/time fields should never be used as a unique key.

Best of luck.

Alter Column from a date to accept systimestamp

1 Answers1