Hi,
We're using Oracle 11.2.
I have a table
CREATE TABLE AERBILL_PROV.DEVICE_STATUS
(
REP_TIMESTAMP TIMESTAMP(3),
DEVICE_ID NUMBER,
ACCOUNT_ID NUMBER NOT NULL,
STATUS NUMBER(2) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE DEFAULT TO_DATE('2999/12/31 23:59:59','YYYY/MM/DD HH24:MI:SS'),
NOT_USED NUMBER(1) DEFAULT 0,
LAST_MODIFIED_DATE DATE,
LAST_MODIFIED_BY VARCHAR2(100 BYTE),
DEVICE_STATUS_ID NUMBER NOT NULL
);
CREATE UNIQUE INDEX AERBILL_PROV.DEVICE_STATUS_PK ON AERBILL_PROV.DEVICE_STATUS
(DEVICE_STATUS_ID) NOPARALLEL;
with the PK index DEVICE_STATUS_PK.
When we try to add the following column
alter table DEVICE_STATUS ADD REQUEST_TIMESTAMP TIMESTAMP(3) DEFAULT SYS_EXTRACT_UTC (SYSTIMESTAMP);
We get the following error
SQL Error [1] [23000]: ORA-00001: unique constraint (.) violated
I was able to get around this by defining a test table (DEVICE_STATUS_ECOL) cloned from DEVICE_STATUS, adding the same column and then adding the PK constraint afterwards. It worked with no errors, so I have a work around.
The people who gave me this error want a root cause. Would anyone be able to tell me why Oracle threw this error for creating a column not related to the PK Column? Could it be a bug?
Any guesses?