The risk of posting a question here, is that I might take it and turn it into a blog post. And that’s where today’s topic comes from.

I imported data dictionary into SQL data modeler and cannot found out how to disable predefined constraint names. Table “T” was created in database without specifying primary key name, so it gained SYS_C0012093 name, but in data modeler constraint is named as “T_PK”. This produces unnecessary statements when I want to merge model into data dictionary. Do you know how to turn this off? I removed all templates from naming standard in Design Properties but it didn’t help.

So let’s draw up a scenario. I’m going to run this in a worksheet:

     (PK_ID INT NOT NULL, STUFF varchar2(20),

Now, when you create a constraint and do not provide a name for it, Oracle will provide a system generated name for you. You can see this in USER_CONSTRAINTS.

It's got a name even if you don't give it one
It’s got a name even if you don’t give it one

Now, let’s drag the table to a model and see what the data modeler comes up with.

Hey, that's not what I called my PK constraint...
Hey, that’s not what I called my PK constraint…

Ok, let’s check the preferences.

That sounds like what is happening here!
That sounds like what is happening here!

So, I like this option. The problem with system generated constraint names is if you see it, you don’t know what it is. SYS_C0040196, SYS_C0040197, SYS_BLAHBLAHBLAH…what are those? But a constraint named, ‘TABLE_PK_NO_CONSTRAINT_NAME_PK’ – you kinda know what is when you see it. Kinda 🙂

So if we disable that preference, delete the table from the model (not the database!), and re-add it to the model from the database, we get this:

Ah, just how I wanted it.
Ah, just how I wanted it.

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

1 Comment

  1. Sunil Kumar Noothi Reply

    This is awesome tip which no one would have known before until some one like you deliver it and mention it.

Write A Comment