Forum Stats

  • 3,780,926 Users
  • 2,254,456 Discussions
  • 7,879,496 Comments

Discussions

Generate modell with data dictorinary without "no inmemory" "NOT DEFERRABLE"

User_84SZK
User_84SZK Member Posts: 35 Red Ribbon

Hello everybody,

when trying to generate my tables from the database with the data modeler version 20.2.0.167, I noticed the following in the DDL code. Additional information is generated that I would like to take out like

->"NO INMEMORY" in create table statement

->"ASC" in create index statement

->"NOT DEFERRABLE" in create constraint statement

See below. How is this possible?

CREATE TABLE myscheama.table (
   id            NUMBER(22) DEFAULT myscheama.seq_A.nextval NOT NULL,
   column1       NUMBER(22) NOT NULL
)
NO INMEMORY;

CREATE UNIQUE INDEX myscheama.tableix ON
   myscheama.table (
       column1
   ASC );

ALTER TABLE myscheama.table 
   ADD CONSTRAINT tablefk FOREIGN KEY ( id )
       REFERENCES myscheama.table2 ( id )
   NOT DEFERRABLE;

Answers

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,367 Employee

    Hello,


    for "in memory" and "NOT DEFERRABLE" you can close physical model and they will disappear. Not possible for ASC


    Philip

  • User_84SZK
    User_84SZK Member Posts: 35 Red Ribbon

    Hello Philipp,

    thank you for your idea.

    But if I close the physical model I lost the default sequence in my create table code.

    This is what i get with ope physical model

    CREATE TABLE myscheama.table (
       id            NUMBER(22) DEFAULT myscheama.seq_A.nextval NOT NULL,
       column1       NUMBER(22) NOT NULL
    )
    NO INMEMORY;
    
    CREATE UNIQUE INDEX myscheama.tableix ON
       myscheama.table (
           column1
       ASC );
    
    ALTER TABLE myscheama.table 
       ADD CONSTRAINT tablefk FOREIGN KEY ( id )
           REFERENCES myscheama.table2 ( id )
       NOT DEFERRABLE;
    

    and this is what i get with closed physical model

    CREATE TABLE myscheama.table (
       id            NUMBER(22) NOT NULL,
       column1       NUMBER(22) NOT NULL
    );
    
    CREATE UNIQUE INDEX myscheama.tableix ON
       myscheama.table (
           column1
       ASC );
    
    ALTER TABLE myscheama.table 
       ADD CONSTRAINT tablefk FOREIGN KEY ( id )
           REFERENCES myscheama.table2 ( id )
       NOT DEFERRABLE;
    
    CREATE SEQUENCE  myscheama.seq_A START WITH 1 NOCACHE ORDER;
    
    CREATE OR REPLACE TRIGGER  myscheama.table_trg BEFORE
        INSERT ON myscheama.table
        FOR EACH ROW
        WHEN ( new.id IS NULL )
    BEGIN
        :new.id:= myscheama.seq_A.nextval;
    END;
    /
    

    I lose this information:

    DEFAULT myscheama.seq_A.nextval
    

    and get an extra trigger.

    Can I change this?

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,367 Employee

    You don't need physical model open to get default clause for identity column. Appearance of trigger depends on setting in preferences. You can set sequence name on identity column in relational model (see the picture) or if you follow name pattern that depends on table/column name or abbreviation then you can set name template in preferences. Sequence always will be created because it doesn't exist in physical model (closed).


    with template in preferences (abbreviations are not set and it takes names):

    CREATE SEQUENCE HR2.JOBS_JOB_ID_SEQAI 
    START WITH 1 
       NOCACHE 
       ORDER ;
    
    CREATE TABLE HR2.JOBS 
       ( 
        JOB_ID    VARCHAR2 (10) DEFAULT HR2.JOBS_JOB_ID_SEQAI.NEXTVAL NOT NULL , 
        JOB_TITLE VARCHAR2 (35) 
            CONSTRAINT JOB_TITLE_NN NOT NULL , 
        MIN_SALARY NUMBER (6) , 
        MAX_SALARY NUMBER (6) 
       ) 
    ;
    

    Philip

    User_84SZK
  • User_84SZK
    User_84SZK Member Posts: 35 Red Ribbon

    Thank you Philip

    this solved my issue :)

  • User_NRYK9
    User_NRYK9 Member Posts: 9 Red Ribbon

    But with this solution (close Physical model) i lose the possibility to set some Storage Properties like INITRANS, that's right?

  • User_84SZK
    User_84SZK Member Posts: 35 Red Ribbon

    Yes and the GRANTS Permission were lost too.

  • User_NRYK9
    User_NRYK9 Member Posts: 9 Red Ribbon

    So it isn't possible set what i want visualize modifying in the Menu Tools -- Preferences -- Code Editor -- Format -- Advanced Format -- Custom Format the code in the Formatting rules Template that i have visualize in the right side of the windows?