MySQL's multi-table design

First, FOREIGN KEY constraints

Ensure the integrity of the data.

To define a FOREIGN KEY constraint:

Foreign keys can be defined directly in the CREATE statement foreign key current table name (field name) References target table name (primary key of the target table)

After you have created the statement, you can define the ALTER table name directly using the Modify statement Add foreign key Current table name (field name) References target table name (primary key of the target table)

Ii. three kinds of entity relationship of multi-table design

Many-to-many, one-to-many, and one-on

Three, multi-table design---------a pair of more

A class can have more than one student, but a student can only belong to one class. Or a department has multiple employees, and one employee belongs to multiple departments. These are a one-to-many relationship, so the design of a one-to-many database is implemented.

Department table

CREATE TABLE Dept (deptid Int primary key auto_increment, dname varchar (+) not NULL);

Employee table

CREATE TABLE EMP (empId int primary key auto_increment, ename varchar (+) not NULL, age int, dno int, con Straint Fk_dno foreign key emp (DNO) References Dept (DeptID));

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/8F/0B/wKiom1jSElGgFtcCAACTCSnQrHM890.png "title=" Ashampoo_snap_2017.03.22_13h57m16s_001_.png "alt=" Wkiom1jselggftccaactcsnqrhm890.png "/>

Four, multi-table design---------Many-to-many

A student can choose multiple courses, and each course can be selected by multiple students. This is a typical many-to-many relationship.

Many-to-many table-building principles:

You need to create a third-party table that has at least two fields as the foreign key that points to the two-to-many primary keys, respectively.

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8F/0F/wKiom1jSWEHzF97KAABeGORsdZg069.png "title=" Ashampoo_snap_2017.03.22_14h28m12s_003_.png "alt=" Wkiom1jswehzf97kaabegorsdzg069.png "/>

This article is from the "11831428" blog, please be sure to keep this source http://11841428.blog.51cto.com/11831428/1909339

MySQL's multi-table design