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

Many to Many

When making the Many to Many relationship, DBD4 automatically inserts an Association table between Table_01 and Table_02. It is named Table_01_has_Table_02.

The primary key from Table_01 and the primary key from Table_02 are inserted as both Primary and Foreign keys in the Association table.

The resulting SQL:

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;

CREATE TABLE Table_01_has_Table_02 ( Table_01_a INTEGER UNSIGNED NOT NULL, Table_02_d INTEGER UNSIGNED NOT NULL, PRIMARY KEY(Table_01_a, Table_02_d), FOREIGN KEY(Table_01_a) REFERENCES Table_01(a) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY(Table_02_d) REFERENCES Table_02(d) ON DELETE NO ACTION ON UPDATE NO ACTION )

TYPE=InnoDB;

Note: Table_02 in the figure has a mistake. The attributes are d, e and f, not a.

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

One to One

Note how d, e and f are all set to be non key attributes.

When making the One to One relationship, DBD4 automatically inserts the primary key from

Table_01 in Table_02 as a new attribute and uses it as the foreign key and primary key in Table_02 as well.

Values in column a in Table_01 can only occur once due to the PK constraint. Like vice values in column Table_01_a in Table_02 can only occur once due to the PK constraint and must be present in column a in Table_01 due to the FK constraint. The one and only one to one and only one relationship is thus assured.

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, Table_01_a), FOREIGN KEY(Table_01_a) REFERENCES Table_01(a) ON DELETE NO ACTION ON UPDATE NO ACTION )

TYPE=InnoDB;

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

Self referencing relationship(s)

The intention is to create two self referencing relationship for Table_01. The following SQL statement is generated. It is a legal SQL statement.

CREATE TABLE Table_01 (

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

FOREIGN KEY(Table_01_a) REFERENCES Table_01(a), FOREIGN KEY(Table_01_a) REFERENCES Table_01(a) )

TYPE=InnoDB;

But the statement only creates one FK and do thus not created the wanted two constraints.

This can be fixed by hand:

CREATE TABLE Table_01 (

a INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Table_01_x INTEGER UNSIGNED NOT NULL,

Table_01_y INTEGER UNSIGNED NOT NULL, /* added by hand */ b INTEGER UNSIGNED NULL, c INTEGER UNSIGNED NULL, PRIMARY KEY(a),

FOREIGN KEY(Table_01_x) REFERENCES Table_01(a), FOREIGN KEY(Table_01_y) REFERENCES Table_01(a) )

TYPE=InnoDB;

The attribute Table_01_a is renamed to Table_01_x and attribute Table_01_y is added by hand. They are then both declared as FK.

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

In Phpmyadmin the table ends up looking like this:

The underlined a is the PK.

When inserting a value into the FKs, Table_01_x and Table_01_y, this is only possible if a already contains the value.

E.g., if no values have yet been entered into the table, so attribute a holds no values, the following insert attempt fails as a is 10, and 11 is inserted into Table_01_y.

But this will work as a first entry:

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