Top Oracle SQL Developer Data Modeler Settings
I've started to use SQL Developer Data Modeler](oracle.com/technetwork/developer-tools/data..) 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.
- 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 > Preferencesin Windows and
Oracle Data Modeler > Preferencesin Mac.
- I did not include any scripting (design rules, transformations, or DDL transformations) in this list. I'll write a separate article on that.
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:
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.