In this post you’ll learn:
- how to create a new Entity Relationship Diagram in Oracle SQL Developer
- how to add tables and columns
- how to relate two tables together
Let’s get started.
Create a New ERD
Oracle SQL Developer includes a tool called Data Modeler that you can use to create and work with ERDs.
You can open this tool by showing the panel with the Data Modeler Objects, which is done by going to View > Data Modeler > Browser.
A Browser panel appears in SQL Developer, which has a range of entry types.
There is a lot of functionality here, but we’ll just cover the basics of creating an ERD.
To do this, we need to create a new Relational Model.
Right-click on the Relational Models type and select New Relational Model.
A new tab is shown with an empty screen.
This looks different to the SQL Editor and includes some toolbar buttons for working with this relational model.
The next step is to add our first table.
Add a Table
To add a table, click on the New Table button on the toolbar, then click anywhere inside the diagram window.
A new window appears that will let you enter data about the new table.
There is a lot of information you can enter here, which makes this tool pretty flexible.
The table has a default name of TABLE_1 but you can (and should) change that.
Change the table name, and click OK.
You’ll be returned to the diagram and the table will be shown as a yellow box on the diagram.
Next, we’ll add some columns.
Double-click on the table and the Table window will open again.
Click on the Columns panel on the left so you can add columns to the table.
Click the green plus button to add a column. Specify the details of the column on the right, such as the name and data type.
You’ll need to change the Data Type to Logical so you’ll get SQL data types in the dropdown.
Once you make changes to the column on the right, you can click on the list of columns to see that your changes have been made.
Click OK, and you’ll see your columns inside the yellow table box on the diagram.
Relate a Second Table
Your ERD will likely need more than one table, so let’s add a second table.
You can do this in the same way as the first table: click the New Table button and click anywhere on the diagram.
We’ll add a second table called car_model, add some columns, and click OK.
The second table is added to the diagram.
How can we link these two tables together?
We can do this by adding a Foreign Key to one of our tables.
Double-click on the table that you want to add the foreign key to. In this example, it’s car_models.
Go to the Foreign Keys tab.
Click on the green plus button to add a new foreign key. A new foreign key is added and named automatically.
Select the referenced table, which in this example is the other table we created.
In the columns list on the left, select the referenced column (the primary key from the other table), as well as the column in this table.
Here’s what it looks like in our example:
Click OK, and the diagram will be updated to show the foreign key on the table, and a line between the two tables to indicate the relationship.
The diagram now has two tables on it and they are related to each other.
Save the ERD File
At any point during your design, you can and should save your file.
To save the file, right-click on the root entry under the Designs folder. In this example, it’s called Untitled_1.
Click Save Design.
Enter a filename for the file and choose a location. Then click Save.
The model file is saved to your computer. The name is also updated in the Browser: in this example, I’ve called the file “SQL Developer model – cars”.
Conclusion
The SQL Developer Data Modeler is a powerful tool, and in this guide, we looked at some of the basics of creating a new ERD.
Hopefully, you found this useful and can use it to create diagrams for your Oracle database.
Thank you. I would like to know if it’s possible to assign tablespaces before generating a DDL script for the schema.