MySQL database, foreign key constraints and table relationships

E-r diagram (Entity Relationship model)

E-r diagram is also called entity-Contact Diagram (Entity Relationship Diagram),
Provides methods for representing entity types, attributes, and connections to describe the conceptual model of the real world.

It is an effective way to describe the conceptual model of real-world relations.

is a way to represent a conceptual relationship model.

Use "Rectangle box" to represent the entity type, the name of the entity is indicated in the rectangle box;

The attribute of the entity is represented by an ellipse frame, and a solid segment is used to connect it with the "solid" of the corresponding relationship;

The "diamond Box" is used to indicate the relationship between the entity type, the contact name is indicated in the Diamond box, and the "solid segment" is connected with the entity type respectively, and

The type of contact on the "solid segment" subscript (1:1,1:n or m:n).

FOREIGN KEY constraints

　　FOREIGN KEY constraint foreign key to maintain data consistency and integrity to achieve one-to-one or one-to-many relationships. (Because a table only has one type of information.) Use foreign keys to make references,

Ensure data consistency and reduce data redundancy

　　Requirements for FOREIGN KEY constraints:

　　　　The storage engine of the data table can only be innodb;

　　　　The foreign key column and the reference column data type are consistent;

　　　　The foreign key must be associated to the key above, and the general case is the primary key associated with the other table.

To create a FOREIGN KEY constraint:

Build a Table A

　　

Build a second table B and give it a foreign KEY constraint:

　　

Statement establishing FOREIGN KEY constraint: Constraint ' foreign key name ' foreign key (ab_id) references ' a ' (a_id);

Foreign KEY (ab_id) references ' a ' (a_id); The foreign key name is given by default and is not duplicated.

Delete foreign key: ALTER TABLE ' B ' drop foreign key dc_id;

　　

Add foreign Key:

　　

Note that there is no foreign key name specified and we can see its name:

One -to-many relationships

For example: A college can have a large number of students, and a student only belongs to one college (usually), the relationship between the college and the students is a pair

Relationships, which are implemented through foreign key associations.

To establish a college table and a student table, the student table has a non-empty field ss_id, which associates it to the institute's number (SCHOOL_ID), i.e. the student must belong to a certain school

Hospital.

　　

　　

To insert data into the school table:

　　

To insert data into the student table:

　　

If the data is inserted, the value of the ss_id is not in the school_id value of the school table and an error is encountered;

One-to-one relationship

For example, students have a school number, name, college, but students have some such as telephone, home address and other more private information, this information will not be placed in the student table when

, a new student's details table will be created to store it. The relationship between the student table and the student's detail table is a one-to-two relationship, because a student only

There is a detailed information. This relationship is achieved by means of a foreign key plus a primary key.

　　

Many-to-many relationships:

For example, students have to enroll in elective courses, one student can enroll in more than one course, and a lot of students enroll in a course, so both the student table and the timetable form many

To many relationships. For a many-to-many relationship, you need to create a third relational table that implements this relationship in the form of a foreign key plus a primary key in the relational table.

Set up the curriculum first:

　　

Then establish the intermediate table Student_course:

　　

The s_id corresponds to the student number of the student's table; The c_id corresponds to the course number of the curriculum.

Some of the more common functions in queries

#求最大年龄 mysql> SELECT MAX (' age ') from ' student_details ';

　　

#求最小年龄:

　　

#其他一些操作:

　　

MySQL database, foreign key constraints and table relationships