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

Source: Internet
Author: User

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]

 

 

 

 

 

 

 

 

 

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.