Hi, I have a database created from existing db scripts. I would like to see the constrain-relationship diagram between all the exsiting tables. Can do that somehow or through some thirdparty tool like Toad , etc. Kindly let me know. Thanks, Anand
there are a few tools offering that. A well-know is Erwin sold by computer associates. It is an expensive product. I could recommend you Dezign for Databases from Datanamic. It is affordable (about 200$) and worth the price and you can do very attractive diagrams and mail them as BMP/JPG. Regards Laurent
You could also try Microsoft Visio.
The commercial version of TOAD has its Master/Detail Browser. This is not diagrammatic and also only supports five levels (great-grandparent -> grandparent -> parent -> child -> grandchild) but if you already pay for TOAD it's an option. Oracle Designer allows us to reverse engineer the database and then produce Entity-Relationship and Database Design diagrams. I imagine JDeveloper does (or will soon) do something similar inna UML stylee. If you already pay for Oracle Developer Suite then those are options. Is this for your personal benefit, or do you need to put it in front of people? In other words, how rough can it be and still be acceptable? Cheers, APC Cheers, APC
the best and most efficient way to see the constraint-relationship is definitely the views DBA_CONSTRAINTS and DBA_CONS_COLUMNS !
Hi 36200 Are you meaning that you want to see something like an "entity relationship Diagrammer" ? . If yes, you can see that easily with Oracle Designer. Joel Pérez
Hi, Thanks a lot for the inputs. I am currently using the master/detail feature of toad. But will also look up the DBA tables as suggested. Thanks, Anand
the best and most efficient way to see the constraint-relationship is definitely the views DBA_CONSTRAINTS and DBA_CONS_COLUMNS ! I'm afraid I disagree. A diagram is much easier to read and shows all the relationships simultaneously. A picture paints a thousand words and all that. Whereas drilling down through DBA_CONSTRAINTS is somewhat mono-dimensional not to mention monotonous. I daresay that if you can play chess blindfold then you could probably manage to hold and manipulate all the information in your head as you plug away in SQL*Plus. However I would agree with this statement the cheapest way to see the constraint-relationship is definitely the views DBA_CONSTRAINTS and DBA_CONS_COLUMNS Keep on truckin' APC
Some colors in your diagram will definitely impress your customers ! However, I feel the dba views more powerful. Regards Laurent SELECT uc.constraint_name, uc2.owner, UC2.TABLE_NAME, UCC2.COLUMN_NAME, UC.OWNER, UC.TABLE_NAME, UCC.COLUMN_NAME FROM user_CONSTRAINTS UC join user_constraints UC2 on (uc.owner = uc2.owner and UC2.constraint_name = UC.r_constraint_name) join user_cons_columns ucc on (uc.owner = ucc.owner and UC.constraint_name = UCC.constraint_name) join user_cons_columns ucc2 on (uc2.owner = ucc2.owner and UC2.constraint_name = UCC2.constraint_name); CONSTRAINT_NAME OWNER TABLE_NAME COLUMN_NAME OWNER TABLE_NAME COLUMN_NAME --------------- ---------- ---------- --------------- ---------- ---------- --------------- FK_DEPTNO SCOTT DEPT DEPTNO SCOTT EMP DEPTNO
Okay. So in real life (as opposed to that Hollywood view of life lived in Scott's schema) DEPT belongs to DIVISION which belongs to REGION. Furthermore, departments can have sub-departments so there is also a foreign key from DEPT to DEPT. DEPT is also used as a foreign key by PROJECT, OFFICE, REGISTRATION, EQUIPMENT, VEHICLE, SECURE_STORAGE and INCIDENT. It is also used in composite foreign keys by their children. EMP on the other hand, obviously has a sdelf-referential link to itself. It also is referenced by CUSTOMER, ORDER, ASSIGNMENT, EQUIPMENT, CHECKOUT and loads of tables in the HR, Accounts and Sales sub-systems. Etc. Now, that is reasonably easy to grok when you are looking at a diagram, particularly if that diagram has pretty colours (they're not just for customers!). I do not think it is as easy to map using DBA_CONSTRAINTS. This is not the same as arguing for vi over TextPad. A database diagram is a genuinely useful tool. Of course it doesn't replace a select on DBA_CONSTRAINTS. Although I will just point out that DBA_CONSTRAINTS only shows you the enforced database integrity. Not all databases have foreign keys, sometimes justifiably, sometimes not. Cheers, APC
ok, let's say you need both, a diagram printed on your A3 laser colorjet and the data dictionnary
A diagram could display the application constraints, when you have no foreign key in the db. To have it on your wall as paper is certainly helpful for the developers. But the data dictionnary is always right, where the diagram could be outdated.
where the diagram could be outdated. Now this is where we get into an argument over whether maintaining the database design in Oracle Designer is the proper way to proceed Cheers, APC
A relatively inexpensive product is DBDesigner4 from fabforce (fabforce.net). Also, you might want to check http://sourceforge.net for some open source products. Some of our developers have used Tora (on Linux) instead of TOAD. I don't know if they have any diagramming tools. HTH. Janet.