Columbus didn’t exactly discover America.

And we’re not really going to discover your long, lost foreign keys.

But, we’ll do our best to guess where they might be.

Our data modeling tool is often the bearer of bad news – your database doesn’t actually have any referential integrity defined.

Oh, it’s defined somewhere, in the application. Probably. Just not in the database. (I realize these aren’t complete sentences, but this is a blog post, not a term paper. If you copy this for your term paper, fix my grammar please.) So development and design tools won’t be able to see them.

Now, I could go on a rant here, but I’ve already done that a few times.

So instead, let’s take a look at what we can do instead.

Some code so you can play along at home:

-------------------------------------------------------- -- DDL for Table CHILD1 -------------------------------------------------------- CREATE TABLE "CHILD1" ( "COLUMN1" NUMBER , "COLUMN2" VARCHAR2 ( 20 ) , "COLUMN3" VARCHAR2 ( 20 ) ) ; -------------------------------------------------------- -- DDL for Table CHILD2 -------------------------------------------------------- CREATE TABLE "CHILD2" ( "COLUMN1" NUMBER , "COLUMN2" VARCHAR2 ( 20 ) , "COLUMN3" VARCHAR2 ( 20 ) ) ; -------------------------------------------------------- -- DDL for Table DADDY_TABLE -------------------------------------------------------- CREATE TABLE "DADDY_TABLE" ( "COLUMN1" NUMBER , "COLUMN2" VARCHAR2 ( 20 ) , "COLUMN3" VARCHAR2 ( 20 ) ) ; -------------------------------------------------------- -- DDL for Index DADDY_TABLE_PK -------------------------------------------------------- CREATE UNIQUE INDEX "DADDY_TABLE_PK" ON "DADDY_TABLE" ( "COLUMN1" ) ; -------------------------------------------------------- -- Constraints for Table DADDY_TABLE -------------------------------------------------------- ALTER TABLE "DADDY_TABLE" MODIFY ( "COLUMN1" NOT NULL ENABLE ) ; ALTER TABLE "DADDY_TABLE" ADD CONSTRAINT "DADDY_TABLE_PK" PRIMARY KEY ( "COLUMN1" ) USING INDEX ENABLE;

If you execute this, and then take those tables and import them to a new design in the modeler, you’ll get something a bit like this:

So what we have are 3 tables. In one table, I have a PRIMARY KEY, ‘COLUMN1″ of type ‘NUMBER.’ I have two other tables with the same column name and data type definition.

Now maybe, JUST maybe the architect behind this mess has decided to carry over column names. We could infer that these tables are indeed related, and that those values across the three tables could be common.

A record in CHILD2 could be followed back to DADDY_TABLE where CHILD2.COLUMN1 = DADDY_TABLE.COLUMN1. And the same inference could be made for the CHILD1 table.

If that is correct, then wouldn’t it be nice to see this visually in our diagram?

Discovering Relationships

Right click on your relational model in your design tree.

So, assuming we have a primary key column in a table and assuming we have columns of the same type and name found in OTHER tables, this will help us find those.

And confirm to see what happens…

We can now save our model and compare it back to the database, and generate the ALTER SCRIPTS to put those FKs in the system.

-- Generated by Oracle SQL Developer Data Modeler 4.1.0.881 -- at: 2015-08-18 12:31:36 EDT -- site: Oracle Database 12c -- type: Oracle Database 12c ALTER TABLE HR . CHILD1 ADD CONSTRAINT CHILD1_DADDY_TABLE_FK FOREIGN KEY ( COLUMN1 ) REFERENCES HR . DADDY_TABLE ( COLUMN1 ) NOT DEFERRABLE ; ALTER TABLE HR . CHILD2 ADD CONSTRAINT CHILD2_DADDY_TABLE_FK FOREIGN KEY ( COLUMN1 ) REFERENCES HR . DADDY_TABLE ( COLUMN1 ) NOT DEFERRABLE ;

I wouldn’t add these constraints before testing the application. Who knows what other assumptions the developers have made. Maybe those relationships aren’t as strong as a FK would make it in the database. But at least you can get your pretty pictures and start to wrap your mind around the data model.

If you DO NOT add these, then when you go to look at the Model page for these three tables in Oracle SQL Developer, you won’t see the related table, or the lines, because again, they’re defined somewhere OTHER than in the database.