MacLochlainns Weblog

While SQL Developer is a nice tool and free, Toad is still an awesome development platform, albeit for Windows. In fact, it was annoying to have to install the Microsoft .NET Framework before installing it. It is free for MySQL!

Since my students have to do all their work in Oracle and then port it to MySQL, I demonstrate Quest’s Toad for MySQL’s at the end of the term. I don’t want them to leverage the automatic ERD diagramming while they’re learning how to do it.

There’s only one real trick to making automatic ERD diagramming work. That trick requires that you write your loading scripts for the Inno DB and use referential integrity constraints. My sample Video Store scripts for my database class are updated for MySQL referential integrity.

Unlike the friendly CASCADE CONSTRAINTS clause you can use in Oracle, MySQL won’t let you create a re-runnable script with only DDL statements. Actually, the constraint comes from the InnoDB engine. You must issue a specialized InnoDB command before running your script:

11
12
13

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0;

Primary keys are a bit different from Oracle and it appears you can’t name them, at least I couldn’t see how to do it. Here’s an example of primary and foreign key constraints in MySQL. The primary key is inline and the foreign key constraints are out of line. This example from the downloadable scripts uses self referencing foreign key constraints.

24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

CREATE TABLE system_user
( system_user_id              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)     NOT NULL
, system_user_group_id        INT UNSIGNED NOT NULL
, system_user_type            INT UNSIGNED NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(20)
, last_name                   CHAR(20)
, created_by                  INT UNSIGNED NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             INT UNSIGNED NOT NULL
, last_update_date            DATE         NOT NULL
, KEY system_user_fk1 (created_by)
, CONSTRAINT system_user_fk1 FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, KEY system_user_fk2 (last_updated_by)
, CONSTRAINT system_user_fk2 FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Once installed (instructions are here) and connected to the MySQL database, you simply click the ERD icon in the top panel and drag the tables onto the canvas. You’ll see something like this (by the way click on the image to see its full size):

Have fun with it. It’ll be interesting to see how Oracle positions MySQL when they own it. My hunch is that they’ll continue to sell it and provide it as an open source product.