3 Replies Latest reply: Mar 31, 2014 11:57 PM by onkar.nath RSS

    ora-00001 primary key

    user11301181

      CREATE TABLE controls (

      ID                         VARCHAR(12) NOT NULL,

      controldate         VARCHAR(10) NOT NULL,

      controltime        VARCHAR(5) NOT NULL,

      controlplace       VARCHAR(50) NOT NULL,

      judgement          NUMBER(4),

      judgedate           VARCHAR(10),

      crime                  VARCHAR(14),

      infraction           VARCHAR(20)

      );

       

       

      CREATE TABLE REGISTER

      (

        rdate VARCHAR2(10) NOT NULL,

        ID VARCHAR2(12) NOT NULL,

        rhour VARCHAR(5) NOT NULL,

        rplace VARCHAR2(50) NOT NULL,

        CONSTRAINT PK_REGISTER PRIMARY KEY(fecha,CID),

        );

       

      CREATE TABLE EVIDENCES

      (

        edate VARCHAR2(10) NOT NULL,

        eID VARCHAR2(12) NOT NULL,

        crime VARCHAR2(14),

        infraction VARCHAR2(20) NOT NULL,

        idreg NUMBER(4) NOT NULL,

        dateregVARCHAR2(10) NOT NULL,

        CONSTRAINT CK_idreg CHECK (idreg>0),

        CONSTRAINT PK_evidences PRIMARY KEY (idreg, edate),

        CONSTRAINT FK_EVIDENCES FOREIGN KEY (edate, eID) REFERENCES REGISTER(rdate,ID)

      );

       

      the insertion I'm trying is:

       

      insert into evidences (edate,eID,crime, infraction idreg,datereg)

           select distinct (controldate,ID,crime,infraction,judgdement,judgedate) from controls

      where crime is not null and judgement is not null and judgedate is not null;

       

      But there is  error in this showing the message: ORA-00001 at primary key in evidences table.

       

      Controls table have null values and duplicates.

       

      Many thanks in advance.

        • 1. Re: ora-00001 primary key
          Krishna-Oracle

          Evidance table has foreign key (edate, eID) referring table REGISTER.

          While inserting value into table Evidance, ensure those values exist in Reigster table.
          Otherwise error will be thrown,

          • 2. Re: ora-00001 primary key
            Emad Al-Mousa

            ORA-00001: unique constraint (string.string) 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.

             

            ORA-00001: unique constraint (string.string) violated tips

             

             

             

            • 3. Re: ora-00001 primary key
              onkar.nath

              you can do either of the below:

               

              Option 1: create exceptions table (script is in $ORACLE_HOME\rdbms\admin\utlexcpt.sql). Disable the constraint. Insert the value. Enable the constraint with EXCEPTIONS INTO EXCEPTIONS clause. you will get error still but then select  from EXCEPTIONS table will give you the row creating problem. Once you have the rowid which is creating problem, you can decide whether to change value in your insert statement or delete record already in the table.

               

              Option 2: run the select statement without insert and see if you are getting duplicate value out of the select statement.

              Option 3: use /*+ ignore_row_on_dupkey_index(tab, tab_pk) */. see this Oracle INSERT INTO SELECT(...) DUP_VAL_ON_INDEX exception behavior - Stack Overflow


              Btw, do you think , this insert is correct "insert into evidences (edate,eID,crime, infraction idreg,datereg)", I guess comma(,) is missing between infraction and idreg columns. Also, columns "idreg,datereg" are defined as NOT NULL in EVIDENCES table but columns "judgdement,judgedate" in CONTROLS table are not defined as NOT NULL. you may also have to look into this structural changes or modify your query accordingly so that you don't get NULL as output for these two columns.


              Onkar