Detailed explanation of mysql integrity constraints example

#Example 1:
create table department(
  id int,
  name char(10) unique
);
mysql> insert into department values(1,'it'),(2,'it');
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(1,'it'),(2,'sale');
#The second way to create a unique create table department(
  id int,
  name char(10) ,
  unique(id),
  unique(name)
);
insert into department values(1,'it'),(2,'sale');

# Create the services tablemysql> 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.01 sec)
#Joint unique, as long as two columns of records have one column that is different, it meets the joint unique constraint mysql> insert into services values
  -> (1,'192,168,11,23',80),
  -> (2,'192,168,11,23',81),
  -> (3,'192,168,11,25',80);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from services;
+------+---------------+------+
| id | ip | port |
+------+---------------+------+
| 1 | 192,168,11,23 | 80 |
| 2 | 192,168,11,23 | 81 |
| 3 | 192,168,11,25 | 80 |
+------+---------------+------+
3 rows in set (0.00 sec)
mysql> insert into services values ​​(4,'192,168,11,23',80);
ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'

# Create student
create 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 recordsmysql> insert into student(name) values ​​('老白'),('小白');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | Lao Bai | male |
| 2 | Xiaobai | male |
+----+--------+------+
rows in set (0.00 sec)

mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | Lao Bai | male |
| 2 | Xiaobai | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+--------+--------+
rows in set (0.00 sec)
# Inserting a record without specifying an id again will continue to grow from the previous last recordmysql> insert into student(name) values ​​('大白');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | Lao Bai | male |
| 2 | Xiaobai | male |
| 4 | asb | female |
| 7 | wsb | female |
| 8 | Dabai | male |
+----+--------+--------+
rows in set (0.00 sec)

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;
+----+------+------+
| id | name | sex |
+----+------+------+
| 9 | ysb | male |
+----+------+------+
row in set (0.00 sec)
#You should use truncate to clear the table. Compared with delete, which deletes records one by one, truncate clears the table directly. 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

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
rows in set (0.02 sec)

# Step length auto_increment_increment, default is 1
# The starting offset auto_increment_offset, the default is 1
# Set the step size to session settings, which is only valid in this connection. set session auto_increment_increment=5;
#Global step size settings are valid.
set global auto_increment_increment=5;
# Set the starting offset set global auto_increment_offset=3;

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=1 before
mysql> select * from student;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | xiaobai | male |
+----+---------+------+
row in set (0.00 sec)
# The next time you insert, start at position 3 and insert record id + 5 each time
mysql> insert into student(name) values('ma1'),('ma2'),('ma3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | xiaobai | male |
| 3 | ma1 | male |
| 8 | ma2 | male |
| 13 | ma3 | male |
+----+---------+------+

#1. When creating a table, create the associated table first, then create the associated table# Create the associated table first (dep table)
create table dep(
  id int primary key,
  name varchar(20) not null,
  describe varchar(20) not null
);
# Create an 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 constraint);
#2. When inserting records, first insert records into the associated table, then insert records into the associated table insert into dep values
(1,'IT','IT Technology Limited Department'),
(2, 'Sales Department', 'Sales Department'),
(3,'Finance Department','Department that Spends Too Much Money');
insert into emp values
(1,'zhangsan',18,1),
(2,'lisi',19,1),
(3,'egon',20,2),
(4,'yuanhao',40,3),
(5,'alex',18,2);

#Logically speaking, if a department in the department table is deleted, the related records in 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 there is no problem in deleting the current department after deleting the records in the employee table mysql> delete from emp where dep_id =3;
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 | description |
+----+-----------+----------------------+
| 1 | IT | IT Technology Limited |
| 2 | Sales Department | Sales Department |
+----+-----------+----------------------+
2 rows in set (0.00 sec)

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 update);

# Delete the records in the associated table (dep), and 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 | description |
+----+-----------+----------------------+
| 1 | IT | IT Technology Limited |
| 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)
#Change the records in the associated table (dep) and the records in the associated table (emp) as well.mysql> update dep set id=222 where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# Check quickly to see if both tables have been deleted and modifiedmysql> select * from dep;
+-----+-----------+----------------------+
| id | name | description |
+-----+-----------+----------------------+
| 1 | IT | IT Technology Limited |
| 222 | 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 | 222 |
| 5 | alex | 18 | 222 |
+----+----------+-----+--------+
4 rows in set (0.00 sec)