Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00001: unique constraint (PK) violated (Oracle 10g)
ORA-00001: unique constraint (PK) violated [message #327380] Mon, 16 June 2008 04:46 Go to next message
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 #327383 is a reply to message #327380] Mon, 16 June 2008 04:48 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Use a sequence. If it is possible for two rows to be inserted at exactly the same time, then your dt column is not a candidate for Primary key.
Re: ORA-00001: unique constraint (PK) violated [message #327391 is a reply to message #327380] Mon, 16 June 2008 05:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #327397 is a reply to message #327391] Mon, 16 June 2008 05:15 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Not with a data datatype you can't, no. Timestamps will accept a precision of 9 (i.e. down to milliseconds) but Date datatypes only store down to seconds.
Re: ORA-00001: unique constraint (PK) violated [message #327399 is a reply to message #327380] Mon, 16 June 2008 05:19 Go to previous messageGo to next message
flyboy
Messages: 1832
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.
Re: ORA-00001: unique constraint (PK) violated [message #327409 is a reply to message #327380] Mon, 16 June 2008 05:47 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks for all replies.
I will create the sequence and will use it in the application.

Thanks
Re: ORA-00001: unique constraint (PK) violated [message #327442 is a reply to message #327380] Mon, 16 June 2008 07:49 Go to previous messageGo to next message
jaganerp@gmail.com
Messages: 63
Registered: April 2008
Member
in date datatype u cont store time for this u will use time stamp
data type;
Re: ORA-00001: unique constraint (PK) violated [message #327443 is a reply to message #327380] Mon, 16 June 2008 07:51 Go to previous messageGo to next message
jaganerp@gmail.com
Messages: 63
Registered: April 2008
Member
i thought u cont abul to store time in date datatype colum instend of taht data type u will use timestamp data type
Re: ORA-00001: unique constraint (PK) violated [message #327444 is a reply to message #327443] Mon, 16 June 2008 07:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jaganerp@gmail.com wrote on Mon, 16 June 2008 14:51
i thought u cont abul to store time in date datatype colum instend of taht data type u will use timestamp data type

Keep it in english please.

Regards
Michel

[Updated on: Mon, 16 June 2008 07:53]

Report message to a moderator

Previous Topic: Reg delete statements
Next Topic: PLS-00049: bad bind variable -- please help
Goto Forum:
  


Current Time: Sun Dec 04 16:34:48 CST 2016

Total time taken to generate the page: 0.07814 seconds