MYSQ database Design (primary key and foreign key)

A primary key can be a property of a real entity, but a common good solution is to use a property that is not related to entity information as a unique indicator (plus ID field)
Primary key is not related to business logic and is used only to mark Records

You can define multiple columns of primary key (combined primary key) After you define a field: Primary key (Id,name,age);(not that 3 fields are primary keys.
Because a table can have only one primary key, it could be a primary key combined with 3 fields)


Design:

Two entity tables, the same primary key field exists

If the primary key value of the record is equal to the primary key of the record in another relational table
The two-day record corresponds to 1:1 database design (common information and infrequently used information stored separately) called: Vertical Segmentation Disadvantage: Data redundancy, structural redundancy


1:n, 11-to-many
An entity that corresponds to multiple other entities, such as a class that corresponds to multiple students

Design: On the more side, add a field with the entity flag that points to the column to which the entity belongs

2, N-Many-to-many

Design: Typically, using an intermediate table that represents the correspondence between entities




Foreign key foreign key concept: if one of the entities (student) is a field (class_id),
A foreign key to a student entity that points to (refers to) the primary key of another entity

The entity that is pointed to, called the entity (the primary table), also called the parent entity (parent table) class
The entity that is responsible for pointing, called from the entity (from the table), also called the Child Entity (sub-table) student

Role: Used to constrain entities that are in the relationship, and whether or not a parent table record is associated with it when the child table record is added
How a table should handle related records when deleting or updating a master table record

Define a foreign key: on the table, add a field that points to the primary key of the primary table,

At last add foreign key (foreign key field) Regerences Itcast_class (class_id) on delete set null
Sets the character set character set UTF8;

Set CASCADE operations
How the table data associated with the primary table data should be handled when it changes
Main Table Update:
Primary table Delete
Use keywords
On update
On delete

allowable cascade Actions
Cascade associated operation, if the primary table is followed by new or deleted, then the corresponding action is also taken from the table
Set NULL setting is NULL, indicating that no primary table record is pointed from the table
Restrict rejecting the main table related operations

ALTER TABLE XXX Add foreign key (yyy_id) references yyy (yyy_id)
XXX: Indicates that the primary table yyy_id represents a foreign key from table yyy

Drunk, and see now that these are generally not--!!! in the project

Only InnoDB supports foreign keys

MYSQ database Design (primary key and foreign key)