MySQL database 8 (ix) Column property's primary key, self-growth

Primary key

As the name implies, the primary key, primary key, is in a table, has only one field, and the value inside has uniqueness

Create a primary key

Create with Table

The system provides two ways to increase the primary key:

1. Add the primary key property directly after the field you want to use as the primary key to determine the primary key

2. Add primary key option after all fields: Primary key (Field information)

Increase after table

Basic syntax: ALTER TABLE name add primary key (field);

View PRIMARY Key

Scenario 1: View the table structure

Scenario 2: View creation statements for a table

Delete primary key

Basic syntax: ALTER TABLE name drop PRIMARY key;

Composite PRIMARY Key

PRIMARY KEY constraint

Once the primary key is increased, there is a requirement for the corresponding field data:

1. The data corresponding to the current field cannot be empty.

2, the data corresponding to the current field can not have any duplication

Primary KEY classification

The primary key classification uses the business meaning classification of the fields corresponding to the primary key:

Business PRIMARY key: The field where the primary key is located, with business significance (Student ID, course ID)

Logical primary key: integral type with natural growth (wide application)

Self-growth

autogrow: Auto Increment, when a field's properties are given, the data in that column is automatically incremented based on previously existing data when no determination data is provided.

Typically auto-grow for logical primary keys

Principle

The principle of automatic growth:

1, in the system has maintained a set of data, used to save the current use of the auto-growth attribute of the field, remember the current corresponding data value, given a specified step size.

2, when the user to insert data, if there is no value given, the system to the original value of the step into the new data

3, auto-growth trigger: The field of the given attribute does not provide a value

4. Automatic growth only applies to numerical values

Use auto-Grow

Basic syntax: Add an attribute auto_increment after the field;

Insert data: Triggers autogrow, cannot be given a specific value (can give null)

Modify Auto-Grow

1. View self-growth: self-growth once triggered, an option is automatically added to the table option (a table can have a maximum of one self-growth)

2, table options can be modified by the table structure to achieve

Basic syntax: ALTER TABLE name auto_increment = value;

Delete and increase auto-growth

Delete self-growth: The auto_increment is no longer retained after the field properties, and when the user modifies the field from the growth, the system automatically clears the self-growth if the auto_increment attribute is not seen.

Delete self-growth: ALTER TABLE my_auto modify ID int;

Increased self-growth: ALTER TABLE My_auto modify ID int auto_increment;

Initial setup

In the system, there is a set of variables to maintain the self-growing initial values and steps

View: Show variables like ' auto_increment% ';

Modify the self-growth step and initial values:

Set auto_increment_increment = value;

Set auto_increment_offset = value;

Detail questions

1. There is only one self-growth in a single table: Self-growth will rise to the table option.

2, if the data in the insertion does not trigger self-growth (given the data), then the self-growth will not behave, after the user specified data, self-growth does not participate, but self-growth silently based on the current user-defined values to initialize the next value.

3, since the growth at the time of modification, the value can be larger, but not more than the current self-growth field value is small.

MySQL database 8 (ix) Column property's primary key, self-growth