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

This article will explain in detail how to achieve an integrity constraint in mysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The role of constraints: to ensure the integrity and consistency of data

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

Unique

In mysql, it is called single column unique

# examples 1:create table department (id int, name char (10) unique); mysql > insert into department values (1djimit`), (2dje); ERROR 1062 (23000): Duplicate entry 'it' for key' name'# exampl 2:create table department (id int unique, name char (10) unique); insert into department values (1dcmct'), (2dje') # 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')

Joint unique: as long as there are two columns of records, one column is different, which meets the joint unique constraint

# 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.05 sec) 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 is unique As long as there are two columns of records, one of them is different, which conforms to the joint and unique constraint mysql > insert into services values-> (1 recorder 192, 168, 11, and 23),-> (2), 168, 11, 81, 81),-> (3, 192, 168, 11, and 25). Query OK, 3 rows affected (0.01sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from services +-+ | id | ip | port | +-+ | 1 | 192 rows in set 168 11 rows in set 23 | 80 | 2 | 192 Magi 168 line 11 25 | 81 | 3 | 192 Magi 168 11 25 | 80 | +-+ 3 Magi (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'

Auto_increment

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

If id is not specified, it will grow automatically.

# create studentcreate table student (id int primary key auto_increment,name varchar (20), sex enum ('male','female') default' male'); mysql > desc student +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -- + | id | int (11) | NO | PRI | NULL | auto_increment | | name | varchar (20) | YES | | NULL | | sex | enum ('male' 'female') | YES | | male | | +-+-+ rows in set (0.17 sec) # insert record mysql > insert into student (name) values (' Lao Bai'), ('Xiaobai') Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0mysql > select * from student;+----+ | id | name | sex | +-+ | 1 | Lao Bai | male | | 2 | Xiaobai | male | +-+ rows in set (0.00 sec)

Specify the case of id

Mysql > insert into student values; Query OK, 1 row affected (0.00 sec) mysql > insert into student values; Query OK, 1 row affected (0.01 sec) mysql > select * from student +-insert a record with no specified id again. | 4 | asb | female | | 7 | wsb | +-+ rows in set (0.00 sec) # Will continue to grow mysql > insert into student (name) values ('big white') in the previous last record Query OK, 1 row affected (0.00 sec) mysql > select * from student +-Laobai | male | 2 | Xiaobai | male | 4 | asb | female | 7 | wsb | female | 8 | male | +-+ rows in set (0.00 sec)

For the self-increasing field, the value is inserted after it is deleted with delete, and the field continues to grow according to the position before deletion.

Mysql > delete from student;Query OK, 5 rows affected (0.00 sec) mysql > select * from student;Empty set (0.00 sec) mysql > select * from student;Empty set (0.00 sec) mysql > insert into student (name) values ('ysb'); Query OK, 1 row affected (0.01 sec) mysql > select * from student +-truncate should be used to clear the table, compared with delete to delete records one by one, truncate is a direct clean table, use it when deleting a large table mysql > truncate student Query OK, 0 rows affected (0.03 sec) mysql > insert into student (name) values ('xiaobai'); Query OK, 1 row affected (0.00 sec) mysql > select * from student +-+ | id | name | sex | +-+ | 1 | xiaobai | male | +-+ row in set (0.00 sec) mysql > auto_increment_increment and auto_increment_offset

Check the available words that begin with auto_inc

Mysql > show variables like 'auto_inc%' +-- +-- + | Variable_name | Value | +-+-+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +-- -+-+ rows in set (0.02sec) # 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 start offset set global auto_increment_offset=3

Emphasis: 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 start offset and step size, execute show variables like'auto_inc%' again

It is found that, as before, exit is required 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 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 | +-+

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.

Foreign key

Understand foreign key

Such as the figure above, if a company has many employees and each employee corresponds to a department, these departments will be repeated when filling out the form, which is too redundant

We can separate them.

At this time, there are two tables, one is the employee table, referred to as the emp table (associated table, that is, the slave table). One is the department table, or dep table for short (the associated table, also known as the master table).

# 1. Create the associated table first, and then create 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) / / create constraints); # 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), (2), (2), (2), (2), (3), (2), (3), (2), (2), (2), (2), (2), (2), (2), (2) (2), (2) (2), (2) (2) (2), (2) (2) (2), (2) (2), (2) (2) (2), (2) (2), (2) (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2)

3. Delete tabl

# logically, if a department in the department table is deleted, the associated records of the employee table will be 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_id = 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 | egon | 20 | 2 | 5 | alex | 18 | 2 | + -+ 4 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 technical limited department | | 2 | sales department | sales department | | +-+-- +-- + 2 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.

On delete cascade # synchronous deletion

On update cascade # synchronous updates

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 # synchronously delete on update cascade # synchronous updates); # then delete the records in the associated table (dep), and delete the records in the associated table (emp) as well as 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 technical limited department | | 2 | sales department | sales department | | +-+-- + 2 rows in set (0.00 sec) mysql > select * from emp | +-+ | id | name | age | dep_id | +-+ | 1 | zhangsan | 18 | 1 | 2 | lisi | 19 | 1 | 3 | egon | 20 | 2 | 5 | alex | 18 | 2 | + -+ 4 rows in set (0.00 sec) # then change the record of the associated table (dep) The records in the associated table (emp) also change 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 | | +-+ 2 rows in set (0.00 sec) mysql > select * from emp | + -. -+ 4 rows in set (0.00 sec) that's all about how to implement an integrity constraint in mysql. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.