Define Identity Columns on SQL Modeler 23

I always used JDeveloper for Database Design, but this project doesn't have a new release since 2017 and it's last supported Oracle Version was 12c. Now I have to design a data Base for Oracle 19c so I tried SQL Developer Data Modeler and I've had some problems adapting to this new tool.

I have a table with ID and I want this to be type number and generated always as identity, something like this 

CREATE TABLE my_table (
    id_my_table  NUMBER
        GENERATED ALWAYS AS IDENTITY ( START WITH 1 NOCACHE ORDER )
    NOT NULL,
    name      VARCHAR2(100) NOT NULL,
    ....

Here are the steps to achieve this. 

  1. First, let's make sure that the diagram is using a recent version of Oracle, right click on your model and select properties



    And make sure the RDBMS values are set to Oracle Database 12c or greater



     
  2. Go to Tools > Preferences, then go to Data modeler > Model and check the db Version



  3. Now, got to Data Modeler / Model / Physical and change "Default Identity DDL" value to "IDENTITY clause"

  4.  Now modify your table and set the type of your column to a numeric one like "Numeric"
  5. Double click on your column to access to column properties window
  6.  On General Setting, mark "Auto Increment" and "Identity Column" options


  7.  Now go to "Auto Increment" and uncheck "Generate Trigger" option



  8. You can change the option "Generate" to "by Default" if you want the Identity column is applied if the value is null or "Always" if you want that this can not be set on insert.

Now when you generate the DLL, it will look like this:


Now you can continue with the columns of other tables.... Celebrate that you did it!!!

The featured video for today id "Relax in the City"...  I need to relax after stressing trying to find these options.




Author

Comments

Post a Comment

Popular posts from this blog

View PDF File on APEX (Simple approach)

Image
PDF ( Portable Document Format ) is a popular file format to store digital documents, many applications require to upload PDF documents. Displaying the uploaded document in the same app could be useful for the user because he can review the PDF file without downloading it. I'll show you a simple approach to display the PDF file on an APEX application. This is simple, but it has a problem, it will use the Browser's PDF viewer, it could be not correctly configured on some users and it will not work on mobiles. However, I hope this be useful for you. This is the DEMO app where you can upload a PDF file and view it on the page, if this is not working, check your browser properties. This has been tested on Firefox 73 , Chrome 80, Opera 66, Safari 12 , Not working on Internet Explorer 11 or Edge. WARNING!!!! This will fail with Large PDF Files. If you need to work with large PDF Files, try Daniel's Approach in the Comments Section. PDF Viewer Demo APP If this is what you
Read More »

Set Custom APP Icon on APEX

Image
I am creating my WorkSpace on  apex.oracle.com  to enable an online demo of my posts, then you can check if that is what you want before try it. Creating a new App, there are only 32 different Icons, but there are not Cat Icons. I wanted to add a Custom Icon, I'll explain how to do it. First, you need your icon on vector (.svg) format, you can create it using free tools like  Inkscape , Paid tools like  Illustrator , get it asking a Graphic Designer for help or Searching a free one on Internet. This time Chose the last one and got this  Cat Vector . You can find more on that site. Just remember, it is better if the vector has a squared aspect ratio Rename the .svg file to  app-icon.svg  and then go to  Your App -> Shared Components -> Static Application Files , upload the file and we got it!. our .sgv file is now our app icon and it will appear on APEX WorkSpace, Login Screen and Home Page. But you can notice that in the Application, the icon has a pur
Read More »

ID Basado en trigger y secuencia para todas las tablas - Oracle

Image
En ocasiones todas o la mayoría de las tablas de una base de datos requieren que su ID sea sacado de un trigger el cual llama una secuencia, por lo que tendremos que crear los trigger y las secuencias para esas tablas las cuales pueden ser muchas. esto nos lleva a 2 situaciones, en donde la base de datos no tiene ningún dato, y en donde esta ya tiene algunos, por lo que la secuencia no puede empezar desde 1. Base de Datos sin Datos :P En el primer caso basta con ejecutar el siguiente bloque pl/sql el cual nos darán el script para crear las secuencias y los triggers, podriamos hacerlo todo en un solo loop, pero queremos que el script de las secuencias nos quede en un bloque y el de los triggers en otro, solo por orden. BEGIN FOR r IN ( SELECT table_name FROM user_tables) LOOP dbms_output.put_line ('CREATE SEQUENCE S_'|| r.table_name || ' MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'||chr(
Read More »