Entity Relationship Diagrams tell a story. An employee is assigned one department. A location can house one or more departments. Jeff doesn’t have a boss because he is ‘the man.’ It can be challenging to understand the nature of your data by simply reading through the list of Foreign Key constraints for a table. Who wants to read this
Foreign keys - they do exist!
when you can instead see this
A pretty picture is worth a few dozen DDL scripts?
So how do you go from one to the other?
Today it requires just a few steps:
Open the Data Modeler Browser in SQL Developer
View .. Data Modeler .. Browser
This will give you a new panel, underneath your Connections|Reports panel.
Go to the Relational Models node in the Tree
Right-mouse-click, ‘New Relational Model’
This will give you a blank model workspace.
Select your table(s) from the connection tree and drag them to the model space.
Voila, instant ERD! The only problem is that you will need to KNOW which objects to drag and drop. The Data Modeler will not automatically recursively search for related objects by foreign key constraint and add those tables. However, if you do add tables one at a time, it will auto-generate the relationship if there is one defined in the database.
I know what you’re thinking, ‘Shouldn’t this be easier?’
The answer is ‘Yes, and we’re working on it.’
Stay thirsty my friends!
Update! SQL Developer can automatically add ‘child tables.’
If you hold down CTRL while you drag, it will bring in all related subordinate objects. Thanks for the heads-up Philip (the guy who wrote the code!) For an example, take HR.REGIONS and Ctrl drag it to a model. You’ll get EMPLOYEES, DEPARTMENTS, JOB_HISTORY, etc all auto-added to the model. Pretty slick!
Twitter
RSS
GooglePlus
Facebook
Jul 09, 2012 @ 11:25:35
Jeff doesn’t have a boss because he is ‘the man.’…or may be he is too cool to have boss. I am having fun reading all these fun lines you mix.
Jul 09, 2012 @ 12:17:30
The jokes keep me sane. If someone else enjoys them, that’s just a bonus. Thanks Arif for hanging out and putting up with my lame humor
Aug 02, 2012 @ 22:20:42
Hi Again Jeff, I dont see a How To, on “How To Generate a logical model for Selected Tables in SQL Developer” . I cannot seem to drag and drop the tables onto the window to do an auto-gen of the Logical Model
Aug 02, 2012 @ 22:28:18
Logical models have entities, not tables.
So you’ll drag your table(s) onto your relational model, then engineer that to a logical model. It’s the double blue arrow that points to the left on the main toolbar.
Aug 02, 2012 @ 22:25:18
In SQL Developer, Is it Viable, to inVoke a Very Visiable View (of a Relational Model etc) Via Visio? (Can SQLDev print in Visio format where the objects kin Visio are populated properly?). For example, the models may be Exportable to some XML format, that Visio can read in ? Thanks … pjr
Aug 02, 2012 @ 22:29:22
It may be possible to have Visio import data from an XML document, but I don’t know. We do not have any explicit support for Visio – importing or exporting.
Aug 07, 2012 @ 11:00:20
Jeff,
I have the following Create table from SQL Developer and I am trying to generate an ERD Diagram using 2003 Visio. I was wondering what I need to do to convert the Create Table to an ERD Diagram. Thanks
CREATE TABLE “TT_TEAM”.”HR_REVOKED_SECURITY_ORGS”
(
“ID” NUMBER NOT NULL ENABLE,
“ACAT_CODE” VARCHAR2(6 BYTE) NOT NULL ENABLE,
“APPR_SEQ_NO” NUMBER(3,0) NOT NULL ENABLE,
“USER_ID” VARCHAR2(30 BYTE) NOT NULL ENABLE,
“ACTION_IND” VARCHAR2(1 BYTE) NOT NULL ENABLE,
“ACTIVITY_DATE” DATE NOT NULL ENABLE,
“COAS_CODE” VARCHAR2(1 BYTE),
“ORGN_CODE” VARCHAR2(6 BYTE),
“POSN” VARCHAR2(6 BYTE) NOT NULL ENABLE,
“MANDATORY_APPR_IND” VARCHAR2(1 BYTE),
“APPR_POSN” VARCHAR2(6 BYTE),
“ORGN_MANAGER” VARCHAR2(6 BYTE) NOT NULL ENABLE,
Aug 07, 2012 @ 11:09:01
You can create a new model in SQL Developer, then do a Data Modeler – Import – DDL File. Point it to your CREATE TABLE script.
Or, create the table in the database, then do a Import – Data Dictionary. Point it to the database where you created the table.
Jan 04, 2013 @ 15:16:03
Hi Jeff!
Please tell me how to list the Foreign Key constraints for a table as it’s shown in the first image.
Jan 04, 2013 @ 16:26:40
Browse to your table in the tree and open it.
Go to the constraints page.
This will by default display ALL constraints. What you see in the screenshot is a filtered list of constraints with only ‘Foreign Key’ as the type.
Right click in the Constraint_Type column header and select ‘Filter Column’ and then select ‘Foreign_Key’ and hit.
You’ll see the grid refresh with a list of those constraints only, and the column header will now show a ‘funnel’ icon to indicate the grid entries have a filter applied.
Jan 04, 2013 @ 17:22:37
Thank you for your answer. The problem is the constraints page doesn´t display all constraints. It shows just primary key and check constraints and I don’t know why doesn’t show foreign keys. Any other suggestion? I’m using version 3.2.20.09.
Jan 04, 2013 @ 17:25:47
Not to be the bearer of bad news, but I’m afraid your table doesn’t have any foreign keys. It’s not uncommon for application developers to do this on purpose with the false belief it improves performance.
Jan 04, 2013 @ 18:18:23
In my schema there are hundreds of tables and I’m sure there are plenty of foreign keys. At this moment I’m issuing a query to obtain that information from the data dictionary, but I’d prefer to see it in the SQL Developer GUI.
Jan 04, 2013 @ 18:39:21
Show me the query you’re using to manually query the data dictionary for the foreign key constraints?
It’s possible we have a bug, but I’d like to see proof that you have physical keys first
Jan 07, 2013 @ 15:12:43
Hi Jeff!
Here’s the query:
SELECT table_name, constraint_name, status, owner
FROM all_constraints
WHERE r_owner = :r_owner
AND constraint_type = ‘R’
AND r_constraint_name in
(
SELECT constraint_name
FROM all_constraints
WHERE constraint_type IN (‘P’, ‘U’)
AND table_name = :r_table_name
AND owner = :r_owner
)
ORDER BY table_name, constraint_name
Jan 07, 2013 @ 15:37:34
Ok, but what does this show?
SELECT table_name, constraint_name, status, owner, constraint_type
FROM all_constraints
where table_name = ‘EMPLOYEES’ and owner=’HR’ and constraint_type = ‘R’ — where EMPLOYEES = your table and HR = your schema
This will show the referential integrity constraints for the selected table, do you have any?