Shulou(Shulou.com)06/01 Report--

This article mainly introduces you how to protect mysql data integrity detailed explanation, the content of the article is carefully selected and edited by the author, how to protect mysql data integrity detailed explanation has a certain pertinence, for everyone's reference significance is relatively great, the following with the author to understand the next topic content.

I. introduction

Constraints, like the width of the data type, are optional parameters

Function: to ensure the integrity and consistency of data

It is mainly divided into:

PRIMARY KEY (contention) # identifies this field as the primary key of the table and can uniquely identify the record

FOREIGN KEY (FK) # identifies the field as the foreign key of the table

NOT NULL # indicates that the field cannot be empty

UNIQUE KEY (UK) # identifies that the value of this field is unique

AUTO_INCREMENT # identifies that the value of this field grows automatically (integer type and primary key)

DEFAULT # sets the default value for this field

UNSIGNED # unsigned

ZEROFILL # populated with 0

Description:

# 1. Whether empty is allowed. Default NULL. NOT NULL can be set. Fields are not allowed to be empty and must be assigned a value.

# 2. Whether a field has a default value. The default value is NULL. If the field is not assigned a value when inserting a record, this field uses the default value.

Sex enum ('male','female') not null default' male'

# must be positive (unsigned). Null is not allowed. Default is 20age int unsigned NOT NULL default 20.

# 3. Is it key?

Primary key primary key

Foreign key foreign key

Index (index,unique...)

II. NOT NULL and DEFAULT

Whether it can be nullable. Null means empty, not a string.

Not null-not nullable

Null-Null

Default value, which can be specified when the column is created, and will be added automatically if it is not actively set when inserting data

Create table tb1 (nid int not null defalut 2, num int not null)

Note:

1. The default value can be empty

2. Set not null, which cannot be empty when inserting values.

3. After the id field is set to have a default value, no matter whether the id field is null or not null, you can insert empty space and fill in the default value specified by default by default.

III. UNIQUE

Chinese translation: different. In mysql, it is called single column unique

Example: create a company department table (each company has a unique department)

Mysql > create table department (- > id int,-> name char (10)->); Query OK, 0 rows affected (0. 01 sec) mysql > insert into department values (1 rows affected sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from department +-+-+ | id | name | +-+-+ | 1 | IT | | 2 | IT | +-+-+ 2 rows in set (0.00 sec) # found that it is possible to insert two IT departments at the same time, but this is unreasonable, so we should set the name field to unique to solve this unreasonable phenomenon.

Next, use the constraint unique to set the fields for the company department.

# the first way to create a unique # example 1:create table department (id int, name char (10) unique); mysql > insert into department values (1djinit`), (2jpcit'); ERROR 1062 (23000): Duplicate entry 'it' for key' name'# example 2:create table department (id int unique, name char (10) unique); insert into department values (1memenciit'), (2meme sale') # the second way to create unique: create table department (id int, name char (10), unique (id), unique (name)); insert into department values (1Magnum gift'), (2Magnum sale')

United only:

# create services table mysql > create table services (id int, ip char (15), port int, unique (id), unique (ip,port)); Query OK, 0 rows affected (0.05sec) mysql > desc services +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | Int (11) | YES | UNI | NULL | | ip | char (15) | YES | MUL | NULL | | port | int (11) | YES | | NULL | | + -+ | 3 rows in set (0.01sec) # Union unique As long as there are two columns of records, one column is different, which conforms to the joint and unique constraint mysql > insert into services values (1), (2) (2), (2)), (3), (3), (3), (3), (3), (2), (3), (3), (3), (3), (2), (2), (2), (2), (2), (2), (2), (3). Query OK, 3 rows affected (0.01sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from services +-+ | id | ip | port | +-+ | 1 | 192Magne 168 11Magin23 | 80 | 2 | 192Magin168Magol 11re23 | 81 | 3 | 192Lie168 11Power25 | 80 | +-+-+ -+ 3 rows in set (0.00 sec) mysql > insert into services values (4 '192, 168, 11, 23 and 80) ERROR 1062 (23000): Duplicate entry '192 for key 11 23-80' for key 'ip'

IV. PRIMARY KEY

There is only one primary key in a table in MySQL. You cannot have multiple columns of primary keys, but you can have compound primary keys.

In a table you can:

Single column as primary key

Multiple columns as primary key (compound primary key)

Constraint: equivalent to not null unique, the value of the field is not empty and unique

Storage engine defaults to (innodb): for the innodb storage engine, a table must have a primary key.

Single column primary key:

# create the t14 table, set the primary key for the id field, unique and different records create table T14 (id int primary key, name char (16)); insert into T14 values (1meme xiaomaa'), (2meme xiaohong`); mysql > insert into t14 values (2minewxxx') ERROR 1062 (23000): chemical reaction of Duplicate entry'6' for key 'PRIMARY'# not null + unique, which is equivalent to setting primary keycreate table T15 (id int not null unique, name char (16)) for id; mysql > create table T15 (- > id int not null unique,-> name char (16)->); Query OK, 0 rows affected (0.01 sec) mysql > desc T15 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | | int (11) | NO | PRI | NULL | name | char (16) | YES | | NULL | | +-+-+ 2 rows in set (0.02 sec) |

Compound primary key:

Create table T16 (ip char (15), port int, primary key (ip,port)); insert into T16 values ('1.1.1.2)), (' 1.1.1.2)

5. AUTO_INCREMENT

Constraints: constrained fields are automatically growing, and constrained fields must be constrained by key at the same time

Create table student (id int primary key auto_increment,name varchar (20), sex enum ('male','female') default' male')

1. If id is not specified, it will grow automatically.

2. You can also specify id

3. For the self-increasing field, delete the field with delete, then insert the value, and the field continues to grow according to the position before deletion.

The difference between auto_increment_increment and auto_increment_offset

Check out the available words mysql > show variables like 'auto_inc%' that begin with auto_inc +-- +-+ | Variable_name | Value | +-+-+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +- -+-+ rows in set (0.02 sec) # step size auto_increment_increment The default is the offset auto_increment_offset from the beginning, and the default is the setting step size for the session. Set session auto_increment_increment=5 is only valid in this connection # the global setting step is valid. Set global auto_increment_increment=5; # sets the starting offset set global auto_increment_offset=3;# emphasizes: If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. If the value of auto_increment_offset is greater than the value of auto_increment_increment, the value of auto_increment_offset will be ignored. After setting the initial offset and step size, perform show variables like'auto_inc%'; discovery again as before, you must first exit before login is valid. Mysql > show variables like'auto_inc%' +-- +-+ | Variable_name | Value | +-+-+ | auto_increment_increment | 5 | | auto_increment_offset | 3 | +- -+-+ rows in set (0.00 sec) # because there was a previous record id=1mysql > select * from student +-+ | id | name | sex | +-+ | 1 | xiaobai | male | +-+ row in set (0.00 sec) # the next time you insert it, start at position 3. Insert record id+5mysql > insert into student (name) values ('ma1'), (' ma2'), ('ma3') each time Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from student +-+ | id | name | sex | +-+ | 1 | xiaobai | male | | 3 | ma1 | male | 8 | ma2 | male | | 13 | ma3 | male | +-+ auto_increment_increment and auto_increment_offset

Emptying tables distinguish between delete and truncate:

Delete from T1; # if there is a self-increasing id, the new data will still start with the last one before deletion.

Truncate table T1; has a large amount of data, deletes faster than the previous one, and starts directly from scratch.

VI. FOREIGN KEY

The company has three departments, but has 100 million employees, which means that the department field needs to be stored repeatedly, and the longer the department name, the more wasteful it is.

This time,

Solution:

We can totally define a department table.

Then let the employee information table associate the table, and how to associate it, that is, foreign key

Create two tables:

# 1. Create the associated table before creating the associated table # first create the associated table (dep table) create table dep (id int primary key, name varchar (20) not null, descripe varchar (20) not null); # then create the associated table (emp table) create table emp (id int primary key, name varchar (20) not null, age int not null, dep_id int, constraint fk_dep foreign key (dep_id) references dep (id)) # 2. When inserting a record, insert the record into the associated table first, and then insert the record insert into dep values into the related table (1), (2) 'sales department', 'sales department'), (3) 'finance department', 'spend too much money') Insert into emp values (1), (2), (2), (2), (2), (3), (3), (3), (3), (4), (4), (4), (4), (4), (4), (4), (4), (4), (4), (3), (3), (3), (3), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (3), (3), (3), (4), (4), (4), (4), (4), (4) Delete table # logically, a department in the department table is deleted, and the associated records of the employee table are deleted one after another. Mysql > delete from dep where id=3;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db5`.`emp`, CONSTRAINT `fk_ name` FOREIGN KEY (`dep_ id`) REFERENCES `dep` (`id`) # but after deleting the records in the employee table first, there is no problem in deleting the current department mysql > delete from emp where dep = 3position query OK, 1 row affected (0.00 sec) mysql > select * from emp +-+ | id | name | age | dep_id | +-- + | 1 | zhangsan | 18 | 1 | 2 | lisi | 18 | 1 | 3 | djb | 20 | 2 | 5 | oldniu | 18 | 2 | + -+ rows in set (0.00 sec) mysql > delete from dep where id=3 Query OK, 1 row affected (0.00 sec) mysql > select * from dep +-id | name | descripe | +-+-+ | 1 | IT | IT Technology Limited Department | | 2 | sales Department | | sales Department | +-+-- + rows in set (0.00 sec) |

The operation of deleting table records above is more tedious, according to reason, if a department is cut, the staff of that department will also be laid off. In fact, there is also a very important content when building a table, which is called synchronous deletion and synchronous update.

Next, delete all the two tables you just built, first delete the associated table (emp), and then delete the associated table (dep)

Next:

Repeat the above operation to create the table

Note: add to the associated table

On delete cascade # synchronous deletion

On update cascade # synchronous updates

Modify the emp table:

Create table emp (id int primary key, name varchar (20) not null, age int not null, dep_id int, constraint fk_dep foreign key (dep_id) references dep (id) on delete cascade # synchronous deletion on update cascade # synchronous updates)

The next operation is in line with the situation in our normal life.

# then delete the records in the associated table (dep), and then delete the records in the associated table (emp): mysql > delete from dep where id=3;Query OK, 1 row affected (0.00 sec) mysql > select * from dep +-id | name | descripe | +-+-+ | 1 | IT | IT Technology Limited Department | | 2 | sales Department | | sales Department | +-+-- + rows in set (0.00 sec) mysql > select * from emp | +-+ | id | name | age | dep_id | +-- + | 1 | zhangsan | 18 | 1 | 2 | lisi | 19 | 1 | 3 | djb | 20 | 2 | 5 | oldniu | 18 | 2 | + -+ rows in set (0.00 sec)

# then change the record of the associated table (dep), and the record of the associated table (emp) will also be changed

Mysql > update dep set id=222 where id=2; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: check quickly to see if both tables have been deleted and whether both have been changed mysql > select * from dep +-- + | id | name | descripe | +-+ | 1 | IT | IT Technology Limited Department | 222nd | sales Department | sales Department | +-+ rows in set (0.00 sec) mysql > select * from emp + -. -+ rows in set (0.00 sec)

After reading the above detailed explanation on how to protect the integrity of mysql data, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.