CODEWITHSUNDEEP
oracle-databaseoracle11g
Peter Penzov
Peter Penzov

Reputation: 1626

unique constraint violated on Oracle table

I want to generate random data into Oracle table:

First table

-- TABLE AGENT_HISTORY

CREATE TABLE AGENT_HISTORY(
  EVENT_ID INTEGER NOT NULL,
  AGENTID INTEGER NOT NULL,
  EVENT_DATE DATE NOT NULL
)
/

 CREATE INDEX IX_RELATIONSHIP1 ON AGENT_HISTORY (AGENTID)
/

-- ADD KEYS FOR TABLE AGENT_HISTORY

ALTER TABLE AGENT_HISTORY ADD CONSTRAINT KEY8 PRIMARY KEY (EVENT_ID)
/

Second table 

-- TABLE CPU_HISTORY

CREATE TABLE CPU_HISTORY(
  CPU_HISTORY_ID INTEGER NOT NULL,
  EVENT_ID INTEGER NOT NULL,
  CPU_NAME VARCHAR2(50 ) NOT NULL,
  CPU_VALUE NUMBER NOT NULL
)
/

I tried to create this PL/SQL block:

Error starting at line : 1,690 in command -
BEGIN

FOR loop_counter IN 1..1000
LOOP
   INSERT INTO CPU_HISTORY_ID (CPU_HISTORY_ID, EVENT_ID, CPU_NAME, CPU_VALUE) VALUES (loop_counter, loop_counter, 'cpu1', dbms_random.value(1,100));
END LOOP;

COMMIT;
END;

Error

  Error report -
    ORA-06550: line 5, column 16:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 5, column 4:
    PL/SQL: SQL Statement ignored
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:
    Error starting at line : 1,680 in command -

second PL/SQL block

BEGIN

FOR loop_counter IN 1..1000
LOOP
   INSERT INTO AGENT_HISTORY (EVENT_ID, AGENTID, EVENT_DATE) VALUES (loop_counter, 22, SYSDATE);
END LOOP;

COMMIT;
END;

error

Error report -
ORA-00001: unique constraint (ADMIN.KEY8) violated
ORA-06512: at line 5
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Can you give me some advice where I'm wrong?

Is there another way to generate 1000 rows with random values into the tables?

Upvotes: 1

Views: 1204

Answers (1)

IsidroGH
IsidroGH

Reputation: 2037

The first error if because the table you are inserting in is CPU_HISTORY_ID but it should be CPU_HISTORY.

The second error is probably caused because that table is already populated with data. Did you execute the second PL twice without deleting the data?

Upvotes: 4

Related Questions