How to Preserve System Generated Constraint Names in Oracle SQL Developer Data Modeler

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:

CREATE TABLE TABLE_PK_NO_CONSTRAINT_NAME
     (PK_ID INT NOT NULL, STUFF varchar2(20),
     PRIMARY KEY (PK_ID));

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!

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: