The Data Warrior

Have you lost your keys?

Has this ever happened to you:

You are starting a new project working on an existing database or data warehouse.

Being the great data architect that you are, you of course ask to see the data model.

The response: Data model? What data model?

Or maybe: Why do you need to see that?

Sigh.

So you fight your way through getting access to a copy of the database, get connected, then reverse engineer the database (see here for a presentation on doing that with SQL Developer Data Modeler).

And voila!

A useless diagram of tables with no foreign keys!

Seriously?

So what gives? How can there be no keys in an Oracle database?

You ask, and get the usual answer – “it’s all in the code.”

Great. Like you want to read all the <enter language du jour here> code that some programmers wrote years ago.

So what’s a data architect to do?

Simple – if you are using  SQL developer Data Modeler, that is.

Early on in version 3.x, our friends at Oracle added this really cool utility – Discover Foreign Keys, to help is just this circumstance.

To find this tool you need to right mouse of the name of a relational model in the Browser tab.

Once you select the option, it just runs and adds in any foreign keys it can find. Now when you look at your diagram it will show you new FK lines where there weren’t any before.

I finally got a chance to try this out recently and it worked great,

The model I was looking into had no FKs at all, but did have Primary Keys defined on most of the tables. (The story is that the FKs were not used because it would slow down loading the data).

And apparently, all the FK columns were named the same as the PK columns. Nice.

Not sure how it works in other scenarios, but in this case it works as advertised.

Give it try next time you get one of “those” databases.

UPDATE 03-Dec-2012: I was thinking it would be good if you could tell after the fact that an FK was generated rather than actually in the database. I just found the property for that. If you double click on the FK line then look under Dynamic Properties you will see “createdByFKDiscoverer” set to “true”.  Not the most obvious place to look (I was thinking in the comment might be nice), but at least it is there somewhere. So you might want to build a custom report to find them in the future.

Later.

Kent

P.S. A lot has happened since my last post. This biggest thing is that I got two presentations accepted for the RMOUG 2013 Training Days in Denver next February. It is one of the best user group events in the country. Check it out now for early registration.