1

The item_type table is empty, yet on this:

INSERT INTO item_type (id, name) VALUES (1, 'PHYSICAL')

I get error:

Error: ORA-00001: unique constraint (MYDB.SYS_C0013475) violated

SQLState:  23000
ErrorCode: 1

It seems like it's simply a PK constraint on ITEM_TYPE table, though I can't say for sure as I'm Oracle newbie.

But if it is, why does it prevent inserting into an empty table?

Note: I have been purging all database objects frequently to port the data again (due to administrative rights I cannot delete this db and recreate it).

UPDATE

SquirrelSQL apparently lies about table DDL, this is DDL I got via SQL query to Oracle:

  CREATE TABLE "MYDB"."ITEM_TYPE" 
   (    "ID" NUMBER(*,0) NOT NULL ENABLE, 
    "NAME" NVARCHAR2(2000), 
     PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" 

It's been generated by SQLAlchemy (Python ORM) from class declaration. This has worked without a hitch on PostgreSQL, yet on Oracle it gives me this kind of problem.

5
  • Not sure how you got a table to be created using that datatype definition. For DECIMAL (and NUMBER) the precision should be between 1 and 38 so I would expect you to get an ORA-01727 when you tried to create the table. Just taking generated code (even this code) at face value is risky; you need to understand it as well. Its not an answer but I suggest you look at the definitions and what the data is and make an Oracle-specific script. Towards the end of this page is a section on converting to Oracle datatypes - docs.oracle.com/cd/B28359_01/server.111/b28286/…. Commented Jun 8, 2016 at 12:44
  • 3
    You probably need to look at the definition of constraint via all_constraints. Also make sure there are no uncommitted transactions in your session, which make also cause PK violations during such testing.
    – Raj
    Commented Jun 8, 2016 at 13:15
  • This is what comes from not explicitly naming constraints. If the name was something meaningful instead of MYDB.SYS_C0013475, you might stand some chance of being able to find out what it is!
    – Vérace
    Commented Jun 8, 2016 at 14:54
  • While I agree with explicitly naming constraints, they don't really add any value other than readability. You can always prefix a PK as FK and FK as PK causing more confusion. Looking up catalog views is only your true source.
    – Raj
    Commented Jun 8, 2016 at 15:38
  • 2
    is there another schema with an ITEM_TYPE table, and possibly a public synonym for it? Could be that you're not inserting into the table you think you're inserting into...
    – Philᵀᴹ
    Commented Jun 15, 2016 at 7:07

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.