Timestamps in Oracle can have one of three configurations: timestamp, timestamp with timezone and timestamp with local timezone. At first glance timestamp with timezone and timestamp with local timezone would seem to be the same, and they can be. They can also be different based on what timezone the current session is set to use. Basically, timestamp with timezone uses the timesone or timezone offset of the server where the database is running, and timestamp with local timezone uses the session timezone or timezone offset. For local sessions the timestamp values for both configurations will be the same, however for remote sessions in another timezone using timestamp with local timezone will use the timezone of the remote session. For example, if a user in Los Angeles, CA inserts data into the customer table in Duluth, MN and the timestamp column uses the local timezone offset the timestamp value will reflect California, not Minnesota, time. And because of this timestamp with timezone columns behave differently than timestamp with local timezone columns with respect to unique constraints and primary keys. Let’s look at an example and see what Oracle does.
A table is created containing two columns defined as follows:
SPLEEBO @ umpqua > create table tz_tst_tbl (
2 ts_w_tzone timestamp(9) with time zone,
3 ts_w_ltzone timestamp(9) with local time zone
4 );
Table created.
SPLEEBO @ umpqua >
Data is inserted into both columns:
SPLEEBO @ umpqua > insert into tz_tst_tbl values(systimestamp, systimestamp);
1 row created.
SPLEEBO @ umpqua > commit;
Commit complete.
SPLEEBO @ umpqua >
Now create unique constraints for both columns:
SPLEEBO @ umpqua > alter table tz_tst_tbl add constraint ts_w_ltzone_us unique (ts_w_ltzone);
Table altered.
So far, so good, the column with the local timezone is now unique. What about the column with the system timezone?
SPLEEBO @ umpqua > alter table tz_tst_tbl add constraint ts_w_tzone_us unique (ts_w_tzone);
alter table tz_tst_tbl add constraint ts_w_tzone_us unique (ts_w_tzone)
*
ERROR at line 1:
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key
SPLEEBO @ umpqua >
An interesting turn of events. To make this even more interesting:
SPLEEBO @ umpqua > create unique index ts_w_tzone_us on tz_tst_tbl(ts_w_tzone);
Index created.
SPLEEBO @ umpqua >
SPLEEBO @ umpqua > select index_name, column_name from user_ind_columns where table_name = 'TZ_TST_TBL';
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
TS_W_LTZONE_US TS_W_LTZONE
TS_W_TZONE_US SYS_NC00003$
SPLEEBO @ umpqua >
It appears that using the server timezone offset is ‘off limits’ with a unique constraint or primary key, but perfectly acceptable for a unique index. Proving there are two unique indexes against this table:
SPLEEBO @ umpqua > select * from user_ind_expressions where table_name = 'TZ_TST_TBL';
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
----------------------------------- ----------------------------------- ----------------------------------- ---------------
TS_W_TZONE_US TZ_TST_TBL SYS_EXTRACT_UTC("TS_W_TZONE") 1
SPLEEBO @ umpqua >
Having a unique index on the timestamp with time zone column doesn’t allow the unique constraint to be created:
SPLEEBO @ umpqua > alter table tz_tst_tbl add constraint ts_w_tzone_us unique (ts_w_tzone) using index ts_w_tzone_us;
alter table tz_tst_tbl add constraint ts_w_tzone_us unique (ts_w_tzone) using index ts_w_tzone_us
*
ERROR at line 1:
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key
SPLEEBO @ umpqua >
To possibly understand this the original table is dropped and a new table with all three timestamp configurations is created:
SPLEEBO @ umpqua > drop table tz_tst_tbl purge;
Table dropped.
SPLEEBO @ umpqua >
SPLEEBO @ umpqua > create table t1 (
2 t0 timestamp,
3 tz timestamp with time zone,
4 tl timestamp with local time zone,
5 ts_type varchar2(20)
6 )
7 ;
Table created.
SPLEEBO @ umpqua >
SPLEEBO @ umpqua > insert into t1 values(
2 systimestamp, systimestamp, systimestamp,
3 'sys Timestamp'
4 );
1 row created.
SPLEEBO @ umpqua >
SPLEEBO @ umpqua > commit;
Commit complete.
SPLEEBO @ umpqua >
Looking at the data the confusion remains:
SPLEEBO @ umpqua > select * from t1;
T0
---------------------------------------------------------------------------
TZ
---------------------------------------------------------------------------
TL TS_TYPE
--------------------------------------------------------------------------- --------------------
17-JUN-19 09.54.51.226695 AM
17-JUN-19 09.54.51.226695 AM -06:00
17-JUN-19 09.54.51.226695 AM sys Timestamp
SPLEEBO @ umpqua >
Dumping the column types reveals a difference in the type definition for the timestamp with time zone column:
SPLEEBO @ umpqua > select
2 dump(t0,16),
3 dump(tz,16),
4 dump(tl,16),
5 ts_type
6 from
7 t1
8 ;
DUMP(T0,16)
--------------------------------------------------------------------------
DUMP(TZ,16)
--------------------------------------------------------------------------
DUMP(TL,16)
--------------------------------------------------------------------------
TS_TYPE
--------------------
Typ=180 Len=11: 78,77,6,11,a,37,34,d,83,17,58
Typ=181 Len=13: 78,77,6,11,10,37,34,d,83,17,58,e,3c
Typ=231 Len=11: 78,77,6,11,a,37,34,d,83,17,58
sys Timestamp
SPLEEBO @ umpqua >
Values for timestamp and timestamp with local time zone columns are normalized to the database timezone; values for timestamp with time zone columns do not undergo that normalization. The error description for an ORA-02329 is:
02329, 00000, "column of datatype %s cannot be unique or a primary key"
// *Cause: An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
// on a column of datatype VARRAY, nested table, object, LOB, FILE
// or REF.
// *Action: Change the column datatype or remove the constraint. Then retry
// the operation.
so declaring a column as timestamp with time zone appears to classify this as an object type, a type not allowed in a primary key or unique constraint [none of the other types listed in the error explanation are “sensible” choices]. Notice that the data type definitions for timestamp and timestamp with local time zone appear to be the same (excluding internal functional differences) which is likely why timestamp and timestamp with local timezone types can be part of a unique constraint or a primary key.
Sometimes the most confusing behavior can have a simple explanation, if the time is taken to perform a suitable investigation. It may take some digging but, more often than not, the answer is there to be found.
# # #
