2

Gives me error when I try to insert and it finally inserts after couple of retries with out any error.

SQL Error: ORA-00001: unique constraint (ALERTS2.PK_UP_MODULE_MASTER) violated.

*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.

INSERT statement:

INSERT INTO up_module_master 
            ( 
                        mr_id, 
                        mr_name, 
                        mr_desc, 
                        mr_parent, 
                        created_by, 
                        created_date, 
                        modified_date, 
                        module_type, 
                        module_link, 
                        link_text, 
                        help_text shortcut_link, 
                        shortcut_name, 
                        shortcut_flag, 
                        module_display_name 
            ) 
SELECT max(u1.mr_id)+1, 
       'Notification Engine', 
       'Notification Engine Module', 
       0,1, 
       SYSDATE, 
       '', 
       '', 
       '../../jalsweb/Logout.jsp', 
       'HTTP', 
       '', 
       '', 
       '', 
       0, 
       'Notification Engine' 
FROM   up_module_master u1;

Below is the table definition:

CREATE TABLE "up_module_master" 
  ( 
     "mr_id"               NUMBER (10, 0) NOT NULL ENABLE, 
     "mr_name"             VARCHAR2 (200 byte) NOT NULL ENABLE, 
     "mr_desc"             VARCHAR2 (250 byte), 
     "mr_parent"           NUMBER, 
     "created_by"          NUMBER, 
     "created_date"        TIMESTAMP (6), 
     "modified_date"       TIMESTAMP (6), 
     "module_type"         VARCHAR2 (100 byte), 
     "module_link"         VARCHAR2 (200 byte), 
     "link_text"           VARCHAR2 (250 byte), 
     "help_text"           VARCHAR2 (250 byte), 
     "shortcut_link"       VARCHAR2 (400 byte), 
     "shortcut_name"       VARCHAR2 (100 byte), 
     "shortcut_flag"       NUMBER, 
     "module_display_name" VARCHAR2 (100 byte), 
     "audit_type"          VARCHAR2 (100 byte), 
     "service_id"          NUMBER, 
     "module_regis_type"   NUMBER DEFAULT 1, 
     CONSTRAINT "PK_UP_MODULE_MASTER" PRIMARY KEY ("mr_id"), 
     CONSTRAINT "UP_MODULE_MASTER_UP_MODUL_FK1" FOREIGN KEY ("mr_parent") 
     REFERENCES "up_module_master" ("mr_id") ENABLE 
  )
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
kittu
  • 6,662
  • 21
  • 91
  • 185
  • 5
    Use a sequence. Do **not** use `max() + 1` to generate unique values. –  Mar 11 '15 at 09:28

3 Answers3

4

Looks like MR_ID is not an identity seed. If another process is attempting to insert a record at the same time as you, both will try to insert the same ID and you will get this error. I suggest that you change the column to be auto incrementing. You will need to create a sequence and then a trigger if using pre version 12 or the identity column if using later versions:

Oracle: how to create an identity column?

The link above has all the details for doing this.

Community
  • 1
  • 1
twoleggedhorse
  • 4,938
  • 4
  • 23
  • 38
1

the problem is, that someone other inserted and commited a row with the same key meanwhile consider using a sequence (it looks like the sequence is already defined): SELECT seq_name.nextval, ... FROM dual

Pavel Gatnar
  • 3,987
  • 2
  • 19
  • 29
1

The reason why it's failing is because "select max(mr_id) + 1 ..." is going to be a fixed number for all rows that you're selecting - it's not going to increment for each row in your select statement. (Duh! See comments below!)

As others have said, use a sequence! (Yes, there are other ways you could use to get unique values as part of your select statement, but they'd be the wrong thing to use. Sequences FTW!)

Boneist
  • 22,910
  • 1
  • 25
  • 40