ORA-00001 : Unique Constraint Violated Caused by DATE VS Timestamp(6)

最新推荐文章于 2024-10-21 10:22:26 发布

原创最新推荐文章于 2024-10-21 10:22:26 发布 · 915 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#ORA-00001

Oracle 专栏收录该内容

33 篇文章

订阅专栏

本文探讨了Oracle数据库中使用DATE与TIMESTAMP(6)类型时遇到的唯一性约束冲突问题。当从包含精确到毫秒的时间戳字段迁移到仅支持秒级精度的DATE字段时，可能会出现数据重复的情况。

Create table tt1 (
dt date primary key
);
Create table tt2(
dt timestamp(6) primary key
);
Insert into tt2 values('25-NOV-13 04.18.47.138746000 PM');
Insert into tt2 values('25-NOV-13 04.18.47.66 PM');
commit;

Insert into tt1 select * from tt2;

ORA-00001: unique constraint violated
unique constraint (%s.%s) violated"

This is because Oracle does not store fractional part for DATE data type. These 2 rows are considered duplicate in TT1. This issue could happen in data migration project if you had not noticed the difference between date and timestamp(6) data type.