MySQL 4. Table Operations

constraints:

• The constraint is to ensure the integrity and consistency of the data.
• Constraints are table-level constraints and column-level constraints

Constraints are used only for a single field, called Column-level constraints

Constraints are for two or more than two fields, called Table-level constraints

constraint types (by function):

• Not NULL (non-null constraint)
• PRIMARY key (primary KEY constraint)
• Unique KEY (single constraint)
• Default constraint
• FOREIGN key (FOREIGN KEY constraint)

FOREIGN KEY constraints:

• Maintain data consistency and integrity.
• Achieve one-to-one, many-to-many relationships.

requirements for FOREIGN KEY constraints:

• The parent and child tables must use the same storage engine and prohibit the use of temporary tables
• The storage engine for the data table can only be InnoDB
• The foreign key column and the reference column must have similar data types, where the length of the number or whether the sign bit must be the same, and the length of the character can be different
• Foreign key columns and reference columns must create indexes

Demo:

1#1. First create a database2 CREATE DATABASEtest1;3#2Create a province information table for saving provinces with only the ID and name of the province4 CREATE TABLEProvince (5IdSMALLINTUNSIGNEDPRIMARY KEYAuto_increment,6NameVARCHAR( -) not NULL7 );8#3. Create a second table with ID and name and province information PID9 CREATE TABLETB1 (TenIdSMALLINTUNSIGNEDPRIMARY KEYAuto_increment, OneUsernameVARCHAR( -) not NULL, APidSMALLINTUNSIGNED, - # Set the PID of this table to a foreign key, which is associated with the ID of the province table - FOREIGN KEY(PID)REFERENCESProvince (ID) the);

View Code

MySQL 4. Table Operations