OraFAQ Forum: SQL & PL/SQL » Timestamp Primary key is giving error while inserting.

Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp Primary key is giving error while inserting. (Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Timestamp Primary key is giving error while inserting. [message #352545]

Wed, 08 October 2008 06:08

misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india

Senior Member

Hi,
i created one table having Primary key as timestamp. when i am
trying to insert, it is giving error. It is inserting 1st row
later remaining 9 rows are throwing 'unique constraint violated'
error. I simulated this problem below

set serveroutput on;

DROP TABLE usage_log;

CREATE TABLE use_log
(
  log_timestamp  TIMESTAMP(6),
  stext          VARCHAR2(100 BYTE),
  CONSTRAINT use_log_pk PRIMARY KEY (log_timestamp));

DECLARE
BEGIN
   FOR i IN 1..10
   LOOP
	BEGIN
	      INSERT INTO use_log
	                  (log_timestamp, stext
	                  )
	      VALUES      (SYSTIMESTAMP, CURRENT_TIMESTAMP
	                  );
	      DBMS_OUTPUT.put_line (i||' loop index executed');
	   
	EXCEPTION
	   WHEN OTHERS
	   THEN
	      DBMS_OUTPUT.put_line (SQLERRM);
	      DBMS_OUTPUT.put_line (   TO_CHAR (SYSTIMESTAMP)
	                            || ' '
	                            || TO_CHAR (CURRENT_TIMESTAMP));
	END;
END LOOP;
end;

select count(*) from use_log;

delete from use_log;

Oracle documentation says that we should not make timestamp as
Primary key field, but nowhere i find WHY?

please suggest.

Report message to a moderator

Re: Timestamp Primary key is giving error while inserting. [message #352554 is a reply to message #352545]

Wed, 08 October 2008 06:40

Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You run too fast:

SQL> DECLARE
  2  BEGIN
  3     FOR i IN 1..10
  4     LOOP
  5   BEGIN
  6         INSERT INTO use_log
  7                     (log_timestamp, stext
  8                     )
  9         VALUES      (SYSTIMESTAMP, CURRENT_TIMESTAMP
 10                     );
 11         DBMS_OUTPUT.put_line (i||' loop index executed');
 12      
 13   EXCEPTION
 14      WHEN OTHERS
 15      THEN
 16         DBMS_OUTPUT.put_line (SQLERRM);
 17         DBMS_OUTPUT.put_line (   TO_CHAR (SYSTIMESTAMP)
 18                               || ' '
 19                               || TO_CHAR (CURRENT_TIMESTAMP));
 20   END;
 21  END LOOP;
 22  end;
 23  /
1 loop index executed
ORA-00001: unique constraint (MICHEL.USE_LOG_PK) violated
08/10/2008 13:39:19.336 +02:00 08/10/2008 13:39:19.336 +02:00
3 loop index executed
ORA-00001: unique constraint (MICHEL.USE_LOG_PK) violated
08/10/2008 13:39:19.352 +02:00 08/10/2008 13:39:19.352 +02:00
5 loop index executed
ORA-00001: unique constraint (MICHEL.USE_LOG_PK) violated
08/10/2008 13:39:19.383 +02:00 08/10/2008 13:39:19.383 +02:00
7 loop index executed
ORA-00001: unique constraint (MICHEL.USE_LOG_PK) violated
08/10/2008 13:39:19.398 +02:00 08/10/2008 13:39:19.398 +02:00
9 loop index executed
ORA-00001: unique constraint (MICHEL.USE_LOG_PK) violated
08/10/2008 13:39:19.430 +02:00 08/10/2008 13:39:19.430 +02:00

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  BEGIN
  3     FOR i IN 1..10
  4     LOOP
  5   BEGIN
  6         INSERT INTO use_log
  7                     (log_timestamp, stext
  8                     )
  9         VALUES      (SYSTIMESTAMP, CURRENT_TIMESTAMP
 10                     );
 11         DBMS_OUTPUT.put_line (i||' loop index executed');
 12   dbms_lock.sleep(0.01);
 13   EXCEPTION
 14      WHEN OTHERS
 15      THEN
 16         DBMS_OUTPUT.put_line (SQLERRM);
 17         DBMS_OUTPUT.put_line (   TO_CHAR (SYSTIMESTAMP)
 18                               || ' '
 19                               || TO_CHAR (CURRENT_TIMESTAMP));
 20   END;
 21  END LOOP;
 22  end;
 23  /
1 loop index executed
2 loop index executed
3 loop index executed
4 loop index executed
5 loop index executed
6 loop index executed
7 loop index executed
8 loop index executed
9 loop index executed
10 loop index executed

PL/SQL procedure successfully completed.

See line 12.

Regards
Michel

Report message to a moderator

Re: Timestamp Primary key is giving error while inserting. [message #352564 is a reply to message #352554]

Wed, 08 October 2008 06:55

misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india

Senior Member

but Michel,
putting Sleep is not practical scenario. Let me explain. this procedure is calling from trigger. When a datagrid from frontend,
posted to table then this trigger get executed. We are facing this prob in our development box.
May be we will not face this prob in production box due to network trafic and other stuffs. what do u think...? or we
simple put this sleep statement in this unit.. ?

Report message to a moderator

Re: Timestamp Primary key is giving error while inserting. [message #352571 is a reply to message #352564]

Wed, 08 October 2008 07:10

Frank
Messages: 7901
Registered: March 2000

Senior Member

Use something else as primary key. For example an id-column that gets its value from a sequence

Report message to a moderator

Re: Timestamp Primary key is giving error while inserting. [message #352572 is a reply to message #352564]

Wed, 08 October 2008 07:14

Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

misragopal wrote on Wed, 08 October 2008 13:55

This is why Oracle recommend to not use timestamp as a primary key, it is NOT a primary key by itself.

Regards
Michel

Report message to a moderator

Re: Timestamp Primary key is giving error while inserting. [message #352575 is a reply to message #352572]

Wed, 08 October 2008 07:20

misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india

Senior Member

so thanks Michel and frank.

Report message to a moderator

Re: Timestamp Primary key is giving error while inserting. [message #352601 is a reply to message #352575]

Wed, 08 October 2008 09:36

Ronald Beck
Messages: 121
Registered: February 2003

Senior Member

While I agree that a timestamp probably shouldn't be a primary key, isn't the following declaration a bit restrictive???


  log_timestamp  TIMESTAMP(6),

How does this work versus


  log_timestamp  TIMESTAMP,

Report message to a moderator

Re: Timestamp Primary key is giving error while inserting. [message #352603 is a reply to message #352601]

Wed, 08 October 2008 09:41

Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It depends on the OS.
For instance, Windows supports only up to millisecond, so timestamp(6) is 3 decimals too much.

Regards
Michel

Report message to a moderator

Previous Topic:	General questions
Next Topic:	Adding in a MAX() function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 02:02:52 CST 2024