ThatJeffSmith

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

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.

primary_unique3

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.