DBDesigner+and+MySqlWorkbench+Relations+and+Relationships+implementation+01 - 图文

Creating two entities with no relationship

The following figure shows that two entities or tables are created. These two simple entities or tables will be the basis of the first sections of this guide.

Create the two entities.

The attributes a and d are marked with a yellow key, which is the DBD4 symbol for Primary Key.

? Per Dahlstr?m 12-07-2013 Page 5 of 21

To edit an entity or table, double click the entity:

The first column has automatically been defined as primary key of the table. To remove a column from the primary key click the Key Icon. To add a column to the primary key click onto the Column Icon.

AI: To make a column Automatically Incrementing after each insert, click the columns row in the AI column.

NN: Not Null can be activated and deactivated by clicking.

Flags: Each data type has specific flags. They can be activated and deactivated by clicking.

Further abbreviations used in text:

PK: Primary Key FK: Foreign Key

Across database literature, terms are not user consistently. The following table shows some of the used terms: MODEL SQL FILES ERD Relation Table Entity Relationship Relationship Attribute Column Field Attribute Relation instance Tuple Row Record The MODEL terms will be used in this text:

The table is still to be completed.

? Per Dahlstr?m 12-07-2013 Page 6 of 21

Generate the SQL statements to create the tables

By clicking “Copy Script to Clipboard”, the following SQL statements are sent to the Clip Board and can be pasted into any document.

CREATE TABLE Table_02 (

d INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, e INTEGER UNSIGNED NULL, f INTEGER UNSIGNED NULL, PRIMARY KEY(d) )

TYPE=InnoDB;

CREATE TABLE Table_01 (

a INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, b INTEGER UNSIGNED NULL, c INTEGER UNSIGNED NULL, PRIMARY KEY(a) )

TYPE=InnoDB;

Note how no foreign keys are declared, as the two tables have no interrelationships between them.

? Per Dahlstr?m 12-07-2013 Page 7 of 21

Making interrelation relationships

One to many

When making the One to Many relationship, DBD4 automatically inserts the primary key from Table_01 in Table_02 as a new attribute and uses it as the foreign key.

The resulting SQL:

CREATE TABLE Table_01 (

a INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, b INTEGER UNSIGNED NULL, c INTEGER UNSIGNED NULL, PRIMARY KEY(a) )

TYPE=InnoDB;

CREATE TABLE Table_02 (

d INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Table_01_a INTEGER UNSIGNED NOT NULL, e INTEGER UNSIGNED NULL, f INTEGER UNSIGNED NULL, PRIMARY KEY(d),

FOREIGN KEY(Table_01_a)

REFERENCES Table_01(a) /*This is where the actual reference to attribute a is made*/ ON DELETE NO ACTION ON UPDATE NO ACTION )

TYPE=InnoDB;

If a Primary Key is not defined in Table_01, no Foreign Key in generated in Table_02. In this case the One to Many relationship is not fulfilled!

Wrong!

? Per Dahlstr?m 12-07-2013 Page 8 of 21