Generate Model Oracle remove UUID when a primary key already exists

Support
#1

I have a table that contains a primary key index on a column named “TRAN_ID”. When I run Generate Model, Studio calls my TRAN_ID a Business Attribute. It generates another System Attribute called “ID” containing a UUID. I can NOT turn off the UUID checkbox or delete the ID field. I can not change TRAN_ID to a System Attribute with type of ID.

How do I make Generate Model read that TRAN_ID is really my ID and not create a new one?

#2

Hi,
Sorry for my late answer.

I have checked primary key recognition logic and have not found any mistakes.

So could you please provide some more details:

  1. Which version of Oracle DB do you use?
  2. Which type has the “TRAIN_ID” column?
  3. Are other tables in the database imported correctly?

Could you share the create-script of the table on which the issue occurs? For example:


CREATE TABLE SYSTEM.STRANGEID_TABLEONE (
  TRAIN_ID         VARCHAR2(32 BYTE),
  SOME_FIELD       NVARCHAR2(50),
  ADDITIONAL_FIELD NVARCHAR2(50),
  CONSTRAINT PK_STRANGEID_TABLEONE PRIMARY KEY (TRAIN_ID) USING INDEX TABLESPACE SYSTEM STORAGE (INITIAL 64 K
                                                                                                 NEXT 1 M
                                                                                                 MAXEXTENTS UNLIMITED)
)
TABLESPACE SYSTEM
STORAGE (INITIAL 64 K
         NEXT 1 M
         MAXEXTENTS UNLIMITED)
LOGGING;
#3
  1. Oracle Version 11.2.0.3.4 (yeah, it’s old)
  2. TRAN_ID is a number column
  3. Some did and some did not import correctly

Here is the script it generated:


-- Create new columns
alter table GWTRANS add ( ID varchar2(32) ) ;
update GWTRANS set ID = newid() where ID is null ;
alter table GWTRANS modify ID varchar2(32) not null ;
alter table GWTRANS add constraint PK_GWTRANS primary key (ID) ;

and here is the DDL direct from SQL Developer:


CREATE TABLE "LRUSER"."GWTRANS"
  (
    "TRAN_ID" NUMBER,
    "KEY"     VARCHAR2(20 CHAR),
    "PURCHDATE" DATE,
    "QUANTITY"  NUMBER(10,0),
    "AMOUNT"    NUMBER(10,2),
    "CRED_CARD" VARCHAR2(20 CHAR),
    "CCEXP_DATE" DATE,
    "GATEWAY_ID"      NUMBER,
    "GWPRODS_PROD_ID" NUMBER,
    "BATCHNUM"        VARCHAR2(10 CHAR),
    "REFDATE" DATE,
    "REFAMT"    NUMBER(10,2),
    "SOURCE"    VARCHAR2(2 CHAR),
    "PAYMETHOD" VARCHAR2(2 CHAR),
    "TRANSNUM"  VARCHAR2(10 CHAR),
    "SHIPDATE" DATE,
    "RETCHECK"  CHAR(1 CHAR),
    "PRMORDNUM" VARCHAR2(10 CHAR)
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP 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 "USERS" ;
CREATE UNIQUE INDEX "LRUSER"."GWTRANS$PK" ON "LRUSER"."GWTRANS"
  (
    "TRAN_ID"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 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 "USERS" ;
  CREATE INDEX "LRUSER"."GWTRANS$PROD" ON "LRUSER"."GWTRANS"
    (
      "GWPRODS_PROD_ID"
    )
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 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 "USERS" ;
  CREATE INDEX "LRUSER"."GWTRANS$GWID" ON "LRUSER"."GWTRANS"
    (
      "GATEWAY_ID"
    )
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 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 "USERS" ;
  CREATE INDEX "LRUSER"."GWTRANS$ORDNUM" ON "LRUSER"."GWTRANS"
    (
      "PRMORDNUM"
    )
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 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 "USERS" ;
CREATE OR REPLACE TRIGGER "LRUSER"."BIUR$GWTRANS" before
  INSERT OR
  UPDATE ON gwtrans FOR EACH row BEGIN IF :new.tran_id IS NULL THEN
  SELECT gwid.nextval INTO :new.tran_id FROM dual;
END IF;
END;
/
ALTER TRIGGER "LRUSER"."BIUR$GWTRANS" ENABLE;
#4

Hi,

For primary keys recognition Studio uses the DatabaseMetaData: getPrimaryKeys method from the java.sql package. No custom logic.

In your script, I do not see the PK-constraint creation. Assumably, this is the reason why TRAIN_ID is not recognized as PK during the Model generation.

There are two ways to solve the problem:

  1. Create a PK-constraint on the TRAIN_ID column.
  2. Create the entity manually using the IDE. The code of the Java class should be similar to follows. Do not forget to register it in persistence.xml.

@DesignSupport("{'imported':true}")
@AttributeOverrides({
        @AttributeOverride(name = "id", column = @Column(name = "TRAIN_ID"))
})
@Table(name = "TRAIN_TABLE")
@Entity(name = "test$TrainTable")
public class TrainTable extends BaseIntegerIdEntity {
    private static final long serialVersionUID = 1744358912633411184L;

    @Column(name = "ADDITIONALFIELD", length = 50)
    protected String additionalfield;

    public void setAdditionalfield(String additionalfield) {
        this.additionalfield = additionalfield;
    }

    public String getAdditionalfield() {
        return additionalfield;
....
    }