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

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/638844/viewspace-1061070/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/638844/viewspace-1061070/