Friday, November 27, 2009

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

As Oracle documentation states that "none of the columns in the unique/primary key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique/primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE".

So if you have a table that has a TIMESTAMP WITH TIME ZONE column this column cant have or be part of a unique/primary key constraint. In order to bypass this you can create a unique index including this column.

e.g

create table test (a int primary key,b int, c timestamp with time zone);

alter table test add constraint test_u01 unique (b,c);

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

create unique index test_u01 on test(b,c);

Index created !!!

No comments:

Post a Comment