Shulou(Shulou.com)05/31 Report--

This article mainly introduces the "MySQL database constraints and self-growth sequence is what", in the daily operation, I believe that many people in the MySQL database constraints and self-growth sequence is what the problem, the editor consulted all kinds of information, sorted out a simple and useful method of operation, hope to answer the "MySQL database constraints and self-growth sequence is what" the doubt is helpful! Next, please follow the editor to study!

1. Constraints (constraint)

A constraint is a check rule that is enforced on a table, and when a DML operation is performed, the data must conform to these rules, and if not, it cannot be executed.

The full name of constraint: constraint condition, also known as integrity constraint condition. It can ensure the integrity of the data in the table and the business logic between the data.

Constraints include:

1) non-empty constraint (not null), referred to as NN

2) unique (UK for short)

3) Primary key constraint (primary), abbreviated as competition

4) Foreign key constraint (foreign key), referred to as FK

5) check constraints (check), referred to as CK

1. Non-null constraint (not null)

A non-null constraint is used to ensure that the field value is not null. Null values are allowed by default for any column, but business logic can require that some columns cannot go to null values, so a non-null constraint is used.

Set up when creating a table:

Create table tName (colName1 Type not null,....)

Set a non-null constraint after creating the table (ps: the corresponding fields in the preceding table cannot have null values)

Alter table tableName modify colName type not null

Cancel a non-empty constraint

Alter table tableName modify colName type null

2. Uniqueness constraint (unique)

Used to ensure that fields or combinations of fields do not have duplicate values, but null values are allowed

Set up when creating a table

Create table tableName (colName1 Type1 unique,. );

Set up after table creation

Alter table tableName modify colName Type unique;alter table tableName add constraint constraintName unique (colName)

View the uniqueness constraint name in the table

Show keys from tableName

Delete uniqueness constraint

Drop index uniqueName on tableName

3. Primary key constraint (primary)

Functionally, primary key constraint is equivalent to the combination of non-empty constraint and uniqueness constraint. The primary key field can be a single field or a combination of fields, that is, fields under the primary key constraint are not allowed to have null values or duplicate values. The primary key can be used to uniquely determine a row of records in the table. Only one primary key is allowed in a table, and there is no limit to the number of other constraints.

The principle of selecting primary key

1) the primary key should be data that is meaningless to the system, such as serial number

2) never update the primary key so that the primary key has no other purpose than to uniquely identify a row of records

3) the primary key should not contain dynamically changing data, such as timestamps

4) the primary key should be generated automatically, and there should be no human intervention, so as not to make it have a meaning other than a unique identification line.

5) the primary key should be based on a single column as much as possible.

Create when creating a table

Create table tableName (colName1 Type1 primary key,.)

Create after creating a table

Alter table tableName modify colName Type primary key

Alter table tableName add constraint constraintName primary key (colName)

Delete primary key

Alter table tableName drop primary key

4. Foreign key constraint (foreign key)

Foreign key constraints are defined on the fields of two tables or two fields of a table to ensure the relationship between the two related fields. That is, if field A sets a foreign key constraint, then the value of field A

Must depend on the value that already exists in field B, but it can be null. Field B is required to be a primary key constraint in the table.

The table where field An is located is called the slave table, and the table where field B is located is called the master table. If a value in the master table is dependent on the slave table, the record of the master table is not allowed to be deleted.

Foreign key constraints degrade database performance:

If the DML operation is performed frequently on the table where the foreign key constraint is set, then each DML operation will check the main table, resulting in additional overhead. Another is that foreign key constraints determine the order in which master-slave tables are generated, sometimes affecting business logic. Therefore, foreign key constraints depend on the demand and should be used with caution.

Set up when creating a table

Create table tableName (empno int primary key,ename varchar (20) not null,mgr int,constraint fk_name foreign key (mgr) references tableName (empno));-mgr is a field.

Set up after table creation

Alter table tableName1 add constraint FK_name foreign key (field A) references tableName2 (field B)

Delete foreign key constraint

Alter table tableName drop foreign key fk_name

5. Check constraints (check)

Check constraints are used to force each value on a field to meet the conditions of the check constraint.

Check constraint considerations for mysql:

1) written as check (condition). Mysql passes syntactically, but has no effect on constraints. Other databases such as oracle are valid

2) if it is a condition of set nature, such as one of the values in gender must ('frankthecomm`), enumerations can be used instead of checking constraints.

How to write it: enum ('fancidjimm`)

Set up when creating a table:

Create table tableName (name varchar (20) not null,age int check (age > 0 and age 18);-other database constraints are valid, but mysql is invalid alter table tableName modify gender enum ('faggot pencils');-mysql is valid

Second, the self-growing "sequence" of mysql 1. The concept of sequence

A sequence is a database object used to generate unique numeric values. The value of the sequence is usually automatically generated in increasing or decreasing order, which is used to automatically generate the value of the primary key in the table, which is an efficient way to obtain the unique key value. Usually serves the primary key and is an ordered set of integer values, such as 1, 2, 3, 4, 5, and 5.

Characteristics of 2.mysql

Mysql does not support sequence mechanism, but the auto_increment of mysql can achieve the same effect as sequence mechanism. We call it a self-growing sequence.

1) auto_increment keyword, which is used to do self-increment operation on fields with primary key constraints. 2) the self-growing sequence starts from 1 by default. 3) the number of steps of the self-growing sequence is 14) the starting number can be set.

The usage is as follows:

Set 1:create table tname (tid int primary key auto_increment) when creating a table

.

); set 1: create table tname (

Tid int primary key auto_increment) auto_increment=100; set after building the table: alter table tableName auto_increment=100; cancels self-growth: alter table tablenName change colName colName Type unsigned not null

3. Function: last_insert_id ()

Function: gets the last value of the sequence. Select last_insert_id ()

At this point, the study on "what are the MySQL database constraints and self-growth sequence" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.