Defining Storage Properties for Primary and Unique Columns in SQL Developer Data Modeler

thatjeffsmith SQL Developer 4 Comments

Tell Others About This Story:

primary_unique1Going through our enhancement requests in The Exchange this week, I was struck by how many folks were asking for features that are already there. There were at least three requests for being able to define how the Indexes are handled for table columns defined as Primary Keys or Unique.

Here’s an example that summarizes the general issue:

There is currently no way to specify storage options for unique constraints in a table. Since it is possible to create unique constraints in tablespaces separate from the table itself, the model should reflect that.

So you CAN in fact do this in SQL Developer Data Modeler, but NOT where you might think you can do it.

When saying a column is a Primary Key or has a UNIQUE constraint, the database handles this by creating Indexes. The developer or designer might already have an Index they want to task to handle these constraints, or they might want to explicitly define in the table definition how that Index should be handled.

I’m guessing when someone asks to set the tablespace for a unique – they mean for the resulting Index created by the constraint.

So how do we handle this?

Create a Physical Model

So in the design navigation tree, scroll down and create at least 1 physical model. Then, find your table and its Primary and Unique Keys. Select one, and open the properties.

Using Index - YES!

Using Index – YES!

So you want to say “Yes, I’m going to use an index!” This tells the modeler that you care and you want to have a say in how this will go down. You have a LOT of options. If you’ve already created Indexes in your relational model, you can then reference them in this dialog. AND, if you’ve already defined your tablespaces in your physical design, then you can reference those as well.

Once you’ve said ‘Yes, I want an Index please,’ then the Using Index panel will activate in the Primary Key Properties dialog.


If you had selected ‘By Create Index Statement,’ then you can actually provide the full Index DDL statement in the constraint definition. Feel free to crazy here 🙂

Please don't steal my code, it won't work for anyone but me anyway.

Please don’t steal my code, it won’t work for anyone but me anyway.

Then, if you want to make sure it’s going to generate the objects as you desire, you can preview the table DDL:

Right click on a table, 'DDL Preview'

Right click on a table, ‘DDL Preview’

So to Summarize:

You can set the primary and unique storage parameters, but ONLY in the Physical Model, not the Relational Model.

Tell Others About This Story:

Comments 4

  1. I guess people are still confused on the difference (and purpose) of the Relational Model vs the Physical Model in SDDM. I tend to think of the Relational as the generic database design (though specific to either Oracle or SQL Server in my case) while the Physical is the actual implementation design that includes, as you pointed out, things like storage, logging, and even partitioning schemes (the more “dba” type of specifications). The fact that you can have multiple physical models (like dev, test, prod) for a single Relational model is a pretty cool feature.

    1. thatjeffsmith Post
  2. Hello Jeff. Not sure if this is the correct way, but I don’t know where I could register a bug to Oracle SQL Developer.
    Since the oficcial release of OSD 4.0, the SQL History (F8) is showing messed timestamps.
    For example, my last statement is registered do 05/01/2016 (May 1st 2016).
    My date format is DD/MM/YYYY, but it alwasy worked fine before the update, and I didn’t change anything related to that.
    Any clue?

    Thank you.

    1. thatjeffsmith Post

Leave a Reply

Your email address will not be published. Required fields are marked *