| ORA-00001: unique constraint (PK) violated [message #327380] |
Mon, 16 June 2008 04:46  |
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
|
Senior Member |
|
|
I am having table TEST with DT as column with date datatype and having PRIMARY KEY constraint.
I executed following script in one hit.
INSERT INTO XX VALUES(TO_DATE(TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss:sssss'),'dd/mm/yyyy hh24:mi:ss:sssss'));
--1 row inserted
INSERT INTO XX VALUES(TO_DATE(TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss:sssss'),'dd/mm/yyyy hh24:mi:ss:sssss'));
--ORA-00001: unique constraint (TEST_CYB.PK) violated
--because on the same second of a minute the script is executed
How can i keep the uniqueness of the table?
Thanks,
[Updated on: Mon, 16 June 2008 04:50] Report message to a moderator |
|
|
|
|
|
| Re: ORA-00001: unique constraint (PK) violated [message #327391 is a reply to message #327380] |
Mon, 16 June 2008 05:02  |
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
|
Senior Member |
|
|
That is the better solution.
But on the production there is need to
1. Create an sequence.
2. Add new cloumn into table.
3. Changes in the insert script everywhere in database objects(trigger,procedure,fun,pack) where insert into table is written etc.
Instead of doing this can we have other solution?
I would like to know can we go upto miliseconds while insertion?
Thanks,
[Updated on: Mon, 16 June 2008 05:03] Report message to a moderator |
|
|
|
| Re: ORA-00001: unique constraint (PK) violated [message #327393 is a reply to message #327391] |
Mon, 16 June 2008 05:11 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
| Quote: |
I would like to know can we go upto miliseconds while insertion?
|
Only if you change the datatype from DATE to TIMESTAMP, which might also break dependent database objects.
Is there actually a design reason to have the primary key on the date on the table?
Also, point 3.) isn't necessarily required, as you could add the PK value in an additional trigger on the table without touching any other code.
|
|
|
|
|
|
| Re: ORA-00001: unique constraint (PK) violated [message #327399 is a reply to message #327380] |
Mon, 16 June 2008 05:19 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> I would like to know can we go upto miliseconds while insertion?
It depends on the data type of the column.
You may find their description in the documentation, found eg. online on http://tahiti.oracle.com/.
SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Datatypes
Oracle Built-in Datatypes
The description of DATE datatype (the type of SYSDATE function value): | Quote: | Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.
|
.
By the way, using TO_DATE( TO_CHAR () ) is totally meaningless.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
