Top Oracle SQL Developer Data Modeler Settings

I’ve started to use SQL Developer Data Modeler](http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html)(SDDM) a lot for a project and realized that pretty much everything is configurable. The problem is that there’s no single area to search all the different settings and even the settings’ search boxes don’t filter for all the options. To get around this (and for my own references) I’ve included some settings that I found very useful. I’ll try to keep this post up to date as I continue to explore SDDM.

Notes

  • Jeff Smith has blogged about a lot of the different settings. For a more extensive list check out his blog.
  • When Preferences is mentioned it’s Tools > Preferences in Windows and Oracle Data Modeler > Preferences in Mac.
  • I did not include any scripting (design rules, transformations, or DDL transformations) in this list. I’ll write a separate article on that.

Adding PROMPT Statements in DDL

SDDM can generate DDL statements for you either by clicking the DDL button (1) or synchronizing the model with a schema (2):

It’s always helpful to have prompt statements before each DDL change to help debug where an issue occurred. To do so open Preferences and check Include PROMPT Command (for Oracle only):

All DDL will then include a prompt statement like:

1
2
PROMPT CREATING TABLE 'MY_TABLE';
CREATE TABLE MY_TABLE ...

12c Identity Column or Auto Generate Sequences

Depending on your database version you may want to either have sequences automatically generated for you or not. In my case I was using a 12c instance and was going to use Identity Columns for primary key IDs. I didn’t want sequences for each table to be automatically generated. To toggle this feature open Preferences and change the options in the image below.

More on the Identity Column settings here

DDL Format - Lowercase and Tabs

If you’re really picky on the format of the DDL that is generated you can modify some of the settings in Preferences > Format:

Naming Standard Templates

When adding primary keys (PK) and various other constraints a default name is always generated. You can determine how this name is generated by modifying the design’s properties settings:

More information on different variables can be found here

Short Table Names

In most DB instances all object names are limited to 30 characters (this is changed in 12c but most organizations still haven’t enabled 128 characters). When using naming standards this can cause some issues because just referencing <table_name> may not work as its already 30 characters. Note <table_name> isn’t a real variable in SDDM.

You can define a short name (SDDM calls it an Abbreviation) for each table via the table properties. Setting the Abbreviation is also very helpful when generating DDL statements or constraints from scripts.