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

chncaesar

于 2013-12-15 00:09:58 发布

阅读量882

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： ORA-00001

本文链接：https://blog.youkuaiyun.com/chncaesar/article/details/17318875

Oracle 专栏收录该内容

33 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.