MySQL database Base (ii) (Constraint and Modification data table) (Continuous update)

One, constrain and modify data tables

The role of constraints? 1. Constraints to ensure the integrity and consistency of the data; 2. Constraints are divided into table-level constraints, column-level constraints; 3. Constraint types include: NOT NULL (non-null constraint), PRIMARY key (primary KEY constraint), unique key (UNIQUE constraint), default constraint, FOREIGN Key (foreign key constraint);

Column-level constraints: for only one field; table-level constraints: for two or more than two fields;

1. Requirements parsing for FOREIGN KEY constraints

FOREIGN KEY constraints: 1. Maintain data consistency and completeness; 2. Implement one-to-many relationships;(this is a fundamental reason to call MySQL a "relational" database)

Requirements for foreign KEY constraints : 1.;2.;3.

4. Foreign key columns and reference columns must be indexed, and MySQL will automatically create indexes if the foreign key columns are not indexed;

Child tables: Tables with foreign key columns, parent tables: tables referenced by table, foreign key columns: Columns with foreign key keywords, reference columns: columns referenced by foreign key columns;

to view the properties of a data Table command: SHOW CREATE table Tb_name, from which you can see that the provinces storage engine for the data table is: InnoDB

Shows the creation of the child table (users), the parent table (provinces), where the PID in the child table is a foreign key column, the ID in the parent table is the reference column, and the English reference means "reference"

to view the index command for a data table: Show INDEXES from Tb_name, command end Plus ' \g ', you can display the result of the command execution as a grid , from which you can see that an index has been created on the ID field, because the index is created automatically when the primary key is created ; There are two indexes in the child table users, and there is anindex on the PID field which proves that MySQL will automatically create an index if the foreign key column does not exist .

MySQL database Base (ii) (Constraint and Modification data table) (Continuous update)