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)
Timestamp Primary key is giving error while inserting. [message #352545] Wed, 08 October 2008 06:08 Go to next message
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.
Re: Timestamp Primary key is giving error while inserting. [message #352554 is a reply to message #352545] Wed, 08 October 2008 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Timestamp Primary key is giving error while inserting. [message #352564 is a reply to message #352554] Wed, 08 October 2008 06:55 Go to previous messageGo to next message
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.. ?
Re: Timestamp Primary key is giving error while inserting. [message #352571 is a reply to message #352564] Wed, 08 October 2008 07:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use something else as primary key. For example an id-column that gets its value from a sequence
Re: Timestamp Primary key is giving error while inserting. [message #352572 is a reply to message #352564] Wed, 08 October 2008 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
misragopal wrote on Wed, 08 October 2008 13:55
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.. ?

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

Regards
Michel


Re: Timestamp Primary key is giving error while inserting. [message #352575 is a reply to message #352572] Wed, 08 October 2008 07:20 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

so thanks Michel and frank.

Re: Timestamp Primary key is giving error while inserting. [message #352601 is a reply to message #352575] Wed, 08 October 2008 09:36 Go to previous messageGo to next message
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,


Re: Timestamp Primary key is giving error while inserting. [message #352603 is a reply to message #352601] Wed, 08 October 2008 09:41 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: General questions
Next Topic: Adding in a MAX() function
Goto Forum:
  


Current Time: Sat Dec 10 04:52:07 CST 2016

Total time taken to generate the page: 0.08438 seconds