2626956 - Special unique index behaviors of GTT(global temporary table) - SAP ASE

• Unique index cannot avoid duplicate value if the unique index is created after a row inserted into this GTT (global temporary table) in the same session:

create global temporary table gtt (a int);
insert into gtt values(1);
create unique index idx on gtt(a);
insert into gtt values (1);                               <== duplicate value can be inserted successfully

• Duplicate value cannot be inserted into GTT(global temporary table) even though the unique index has been dropped earlier in the same session:

create global temporary table gtt2 (a int)          <==  create GTT
create unique index idx on gtt2(a)                   <==  create unique index on empty GTT
insert into gtt2 values (10)                             <==  insert one row
drop index gtt2.idx                                        <==  drop the unique index
insert into gtt2 values (10)                             <==  inserting duplicate row failed with Msg 2601

• Unique index behaves normally if logging from another new session and try to insert duplicate value.  i.e duplicate value cannot be inserted when there's unique index and duplicate value can be inserted when unique index has been dropped.

• SAP Adaptive Server Enterprise(ASE) 16 SP03
• SAP Adaptive Server Enterprise Cluster Edition (ASE CE) 16 SP03

GTT, global temporary table, 2601, unique index, duplicate key row , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , BC-DB-SYB , Business Suite on Adaptive Server Enterprise , Problem