Going 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.
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 🙂
Then, if you want to make sure it’s going to generate the objects as you desire, you can preview the table DDL:
So to Summarize:
8 Comments
hi, and may i have set fixed to YES by default the properties USING INDEX before to import a model or create a tabel and its primary key? so for all my primary key i have always the panel using index displayed ON.
thanks in advance
Sorry, I don’t understand your question….
i try to explain better, i’m inside a phisycal design, list of tables, for one table i visualize the properties for Primary Key. One of this is USING INDEX properties i ask if it possibile set to YES by DEFAULT so i can always have preset it to YES and the panel active. i want to avoid having access to primary key properties to activate USING INDEX panel.
Option One: Set it once for your table, and then globally apply that property to every table in your design.
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.
That’s definitely the case. A lot of the modeling tools just have the Logical and Physical Model – we throw in the Relational twist, so it’s an education issue.
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.
You’re in luck – I know of this bug. It’s been fixed for our next update. Stay tuned 🙂
The proper place for bug reports is with My Oracle Support (MOS).