MySQL database BASICS (2) (constraints and modifying data tables) (in constant update), mysql updating

MySQL database BASICS (2) (constraints and modifying data tables) (in constant update), mysql updating

I. Constraints and modify data tables

What is the role of a constraint? 1. constraints ensure data integrity and consistency; 2. constraints are divided into table-level constraints and column-level constraints. 3. constraints include: not null (non-empty constraint), primary key (primary key constraint), unique key (UNIQUE constraint), DEFAULT (DEFAULT constraint), and foreign key (foreign key constraint );

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

1. Analysis of foreign key constraints

Foreign key constraints: 1. Maintain data consistency and integrity; 2. Implement one-to-one or one-to-many relationships;(This is also called MySQL"Relational"A fundamental reason for the database)

Foreign key constraints:1.; 2.; 3.

4. indexes must be created for the foreign key column and reference column. If the foreign key column does not have an index, MySQL will automatically create an index;

Sub-tables: tables with foreign key columns; parent tables: tables referenced by Word tables; foreign key columns: Columns with foreign key keywords added; reference columns: the column referenced by the foreign key column;

Command for viewing data table properties:Show create table tb_name, from which you can see that the storage engine of the data TABLE provinces is: InnoDB

Displays the creation process of the sub-table (users) and parent table (provinces). The pid in the sub-table is the foreign key column, and the id in the parent table is the reference column, reference"

Run the following command to view the index of a data table:Show indexes from tb_name,Add '\ G' at the end of the command to display the command execution result in a grid,You can see that the index has been created on the id field, because the index is automatically created when the primary key is created.;The sub-Table users has two indexes,The index on the pid field proves that''If the foreign key column does not have an index, MySQL will automatically create an index"

2. Reference operations for foreign key constraints

;;

Note: when inserting a record, you must insert a record in the parent table before inserting a record in the child table.

Delete record command:Delete from tb_name WHERE [field name] = [field value]