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.
6 Comments
That was really handy! I’ve had Data Modeler installed for all of 10 mins and I think your blog is going to be really useful this afternoon. Cheers!
Thanks for the feedback Chris! And just let us know if and when you get stuck – we’ll be happy to help if we can.
without creating the FK on a live environment! is there an option to run a quick SQL check of the data within the tables, to proved we don’t have any “children without any daddy’s” and perhaps list them?
First you can create a disabled foreign key on production than enable it and log exceptions to a table with rowids.
create table exceptions(
ROW_ID ROWID
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
)
alter table table1 drop constraint pk_test
alter table table1 add constraint pk_test primary key (category_code)
alter table table2 drop constraint fk_test2
alter table table2 add CONSTRAINT fk_test2 foreign key(old_category_code) references table1(category_code) disable NOVALIDATE
alter table table2 enable CONSTRAINT fk_test2 EXCEPTIONS INTO EXCEPTIONS ;
[1]: ORA-02298: onaylama yapilamiyor (fk_test2) – üst anahtarlar bulunamadi
select*from exceptions
AAEq1EAAJAAIPAOAAA CLFU table2 fk_test2
AAEq1EAAJAAIPAOAAB CLFU table2 fk_test2
AAEq1EAAJAAIPAOAAC CLFU table2 fk_test2
AAEq1EAAJAAIPAOAAD CLFU table2 fk_test2
wow, thanks, learning every friday
And while I was asleep 😀