What are the constraints of MySQL?

1. Overview

Concept: Constraints are rules that act on fields in a table and are used to limit storage in the table The data.

Purpose: Ensure the accuracy, validity and integrity of the data in the database.

Category:

Note: Constraints act on the fields in the table and can be created Table/Add constraints when modifying the table.

2. Constraint demonstration

We have introduced the common constraints in the database and the keywords involved in the constraints. So how do we specify these constraints when creating and modifying tables? Well, next we will demonstrate it through a case.

Case requirements: Complete the creation of the table structure according to the requirements. The requirements are as follows:

##The corresponding table creation statement is:

CREATE TABLE tb_user (
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
	NAME VARCHAR ( 10 ) NOT NULL UNIQUE COMMENT '姓名',
	age INT CHECK ( age > 0 && age <= 120 ) COMMENT &#39;年龄&#39;,
	STATUS CHAR ( 1 ) DEFAULT &#39;1&#39; COMMENT &#39;状态&#39;,
	gender CHAR ( 1 ) COMMENT &#39;性别&#39; 
);

When adding constraints to fields, we only Just add the constraint keyword after the field, and you need to pay attention to its syntax.

We execute the above SQL to create the table structure, and then we can test it through a set of data to verify whether the constraints can take effect.

(1) First, three pieces of data were added.

insert into tb_user(name,age,status,gender) values ('Tom1',19,'1','男'),('Tom2',25,'0','男'); 
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');

It took 21 seconds to add three pieces of data. What is going on?

Originally I thought that adding these constraints caused the slowness of new data. In fact, it is not because this is Alibaba's Linux server, and then I use the client in Linux to It only takes 0.01 seconds to connect to mysql and execute new addition, which shows that this is the time it takes for navicat to connect to the remote host.

Even if these new constraints are added, it will cause the new data to be slow, which can only be clearly noticed in batches. It is basically invisible to a single piece of data.

(2) Test name NOT NULL

insert into tb_user(name,age,status,gender) values (null,19,'1','男');

##(3) Test name UNIQUE (only)

The data added above already contains Tom3. If you add it again, an error will be reported directly.

insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');

Although an error is reported, we will find a phenomenon when we add a piece of data at this time.

insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');

It is obviously a self-increasing ID, but there is no 4. The reason is that UNIQUE is ready to be put into the database after applying for the self-increasing ID. Then at this time, we will first check if there is one in the database. There is a value with the same name. If there is, the new addition fails. Although the new addition fails, the auto-increment ID has been applied for!

On the contrary, when we just tested the null name, he did not apply for an id, because he had already judged that it was empty at the beginning and had not yet reached the step of applying for an id.

Determine whether it is empty -》 Apply for auto-increment id -》 Determine whether there is already an existing value

Summary: When new When the added name is not empty, but is the same as the previously existing data, the new addition will fail at this time, but it will apply for the primary key id.

(4) Test CHECK

We set the age must be greater than 0 and less than or equal to 120, otherwise the save will fail!

age int check (age > 0 && age <= 120) COMMENT &#39;年龄&#39; ,

insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男'); 
insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男');

(5) Test DEFAULT ‘1’ Default value

STATUS CHAR ( 1 ) DEFAULT '1' COMMENT '状态',

insert into tb_user(name,age,gender) values ('Tom5',120,'男');

(6) Above, we complete the constraints by writing SQL statements specified, what if we are Navicat client?

Primary key auto-increment

name unique constraint

status defaults to 1

3. Foreign key constraints

1. What are foreign key constraints

Foreign keys:

are used to A connection is established between the data in the two tables to ensure data consistency and integrity.

Let’s take a look at an example:

#The emp table on the left is the employee table, which stores basic information about employees, including The employee's ID, name, age, position, salary, joining date, supervisor ID, and department ID. Stored in the employee's information is the department's ID dept_id, and the department's ID is the primary key id of the associated department table dept. The dept_id of the emp table is the foreign key, and it is associated with the primary key of another table.

2、 不使用外键有什么影响

通过上面的示例，我们分别来演示 添加外键 和不添加外键的区别，首先来看不添加 外键 对数据有什么影响：

准备数据：

CREATE TABLE dept ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '部门名称' ) COMMENT '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办');

CREATE TABLE emp (
	id INT auto_increment COMMENT 'ID' PRIMARY KEY,
	NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
	age INT COMMENT '年龄',
	job VARCHAR ( 20 ) COMMENT '职位',
	salary INT COMMENT '薪资',
	entrydate date COMMENT '入职时间',
	managerid INT COMMENT '直属领导ID',
dept_id INT COMMENT '部门ID' 
) COMMENT '员工表';

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES 
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1), 
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开 发',11000, '2002-02-05', 2,1), 
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程 序员鼓励师',6600, '2004-10-12', 2,1);

接下来，我们可以做一个测试，删除id为1的部门信息。

结果，我们看到删除成功，而删除成功之后，部门表不存在id为1的部门，而在emp表中还有很多的员工，关联的为id为1的部门，此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。

正常开发当中有时候会通过业务代码来控制数据的不完整性，例如删除部门的时候会先根据部门id去查看一下有没有对应的员工表，如果有则删除失败，没有则删除成功。

3、 添加外键的语法

可以在创建表的时候直接添加外键，也可以对现已存在的表添加外键。

（1）方式一

CREATE TABLE 表名( 
	字段名 数据类型, 
	... 
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) 
);

使用示例：

CREATE TABLE emp (
	id INT auto_increment COMMENT 'ID' PRIMARY KEY,
	NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
	age INT COMMENT '年龄',
	job VARCHAR ( 20 ) COMMENT '职位',
	salary INT COMMENT '薪资',
	entrydate date COMMENT '入职时间',
	managerid INT COMMENT '直属领导ID',
	dept_id INT COMMENT '部门ID',
	CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept (id)  
) COMMENT '员工表';

也可以省略掉CONSTRAINT fk_emp_dept_id 这样mysql就会自动给我们起外键名称。

方式二：对现存在的表添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

使用示例：

alter table emp add constraint fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

方式三：Navicat添加外键

删除外键：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

使用示例：

alter table emp drop foreign key fk_emp_dept_id;

4、 删除/更新行为

我们将在父表数据删除时发生的限制行为称为删除/更新行为，此行为是在添加外键之后发生的。具体的删除/更新行为有以下几种:

默认的MySQL 8.0.27版本中，RESTRICT是用于删除和更新行的行为！但是，不同的版本可能会有不同的行为

具体语法为:

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

就是比原先添加外键后面多了这些ON UPDATE CASCADE ON DELETE CASCADE，代表的是更新时采用CASCADE ，删除时也采用CASCADE

5、 演示删除/更新行为

（1）演示RESTRICT

在对父表中的记录进行删除或更新操作时，需要先检查该记录是否存在关联的外键，如果存在，则不允许执行删除或更新操作。 (与 NO ACTION 一致) 默认行为

首先要添加外键，默认是RESTRICT行为！

alter table emp add constraint fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

删除父表中id为5的记录时，会因为emp表中的dept_id存在5而报错。假如要更新id也同样会报错的！

（2）演示CASCADE

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则

也删除/更新外键在子表中的记录。

删除外键的语法：

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

删除外键的示例：

alter table emp drop foreign key fk_emp_dept_id;

指定外键的删除更新行为为cascade

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

修改父表id为1的记录，将id修改为6

我们发现，原来在子表中dept_id值为1的记录，现在也变为6了，这就是cascade级联的效果。

在一般的业务系统中，不会修改一张表的主键值。

删除父表id为6的记录

我们发现，父表的数据删除成功了，但是子表中关联的记录也被级联删除了。

（3）演示SET NULL

当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;

在执行测试之前，我们需要先移除已创建的外键 fk_emp_dept_id。然后再通过数据脚本，将emp、dept表的数据恢复了。

接下来，我们删除id为1的数据，看看会发生什么样的现象。

我们发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 emp，我们发现子表emp的dept_id字段，原来dept_id为1的数据，现在都被置为NULL了。

This is the effect of the delete/update behavior of SET NULL.

4. Is it better to use auto-increment or uuid as the primary key ID?

When designing tables in mysql, mysql officially recommends not to use uuid or discontinuous and non-repeating snowflake ids (long-shaped and (unique), but it is recommended to continuously increase the primary key id. The official recommendation is auto_increment. So why is it not recommended to use uuid? What are the disadvantages of using uuid?

1. Test uuid, auto-increment id and random number insertion efficiency

First create three tables, user_auto_key represents the auto-increment table, user_uuid represents It is the uuid where the id is stored, and random_key represents the table id which is the snowflake id. Then connect to jdbc to insert data in batches. The test results are as follows:

When the existing data volume is 130W: Let’s test inserting 10w of data again to see what will happen. Result:

It can be seen that when the data amount is about 100W, the insertion efficiency of uuid is at the bottom, and after adding 130W of data in the sequence, the time of uudi plummets again . The overall efficiency ranking of time usage is: auto_key>random_key>uuid, uuid has the lowest efficiency

2. Disadvantages of using auto-incrementing ids

1. Once others crawl your database, they can obtain your business growth information based on the database's auto-increment ID, and it is easy to analyze your business situation

2. For high-concurrency loads , InnoDB will cause obvious lock contention when inserting according to the primary key. The upper bound of the primary key will become a hot spot for contention, because all insertions occur here, and concurrent insertion will lead to gap lock competition

3. The Auto_Increment lock mechanism will cause the grabbing of auto-increment locks, which will cause a certain performance loss

4. It is quite troublesome if auto-increment ID involves data migration!

5. And once it comes to sub-databases and sub-tables, it is quite troublesome to automatically increase the ID!

3. Disadvantages of using uuid

Because uuid has no rules relative to sequential self-increasing IDs, the value of a new row is not necessarily higher than The previous primary key value was larger, so InnoDB could not always insert new rows to the end of the index. Instead, it needed to find a new suitable location for the new row to allocate new space. This process requires performing multiple additional operations, and the disorder of the data may cause data to be scattered, resulting in the following problems:

1. The target page written is likely to have been flushed to the disk and moved up from the cache In addition, or has not been loaded into the cache, innodb has to find and read the target page from disk into memory before inserting, which will cause a lot of random IO

2. Because the write is chaotic In order, innodb has to perform page splitting operations frequently in order to allocate space for new rows. Page splitting leads to the movement of a large amount of data. At least three pages need to be modified at one time.

3. Due to frequent page splitting Split, the page will become sparse and filled irregularly, which will eventually lead to data fragmentation

Page splitting and fragmentation problems, uuid will indeed cause this problem, but Snowflake can solve this problem, The snowflake algorithm is naturally sequential and the newly inserted ID must be the largest, so I think using the snowflake algorithm is a very good choice!

5. Use foreign keys as little as possible in actual development

Primary keys and indexes are indispensable. They can not only optimize the data retrieval speed, but also save developers from other work.

Focus of conflict: Whether the database design requires foreign keys. There are two questions here:

One is how to ensure the integrity and consistency of database data;

The second is the impact of the first one on performance.

Here are divided into two viewpoints: positive and negative, for reference!

1. Positive point of view

1. The database itself ensures data consistency and integrity, which is more reliable, because it is difficult for the program to guarantee 100% data integrity, and it is difficult to use external Even when the database server crashes or other problems occur, the key can ensure the consistency and integrity of the data to the greatest extent.

2. Database design with primary and foreign keys can increase the readability of the ER diagram, which is very important in database design.

3. The business logic explained by foreign keys to a certain extent will make the design thoughtful, specific and comprehensive.

There is a one-to-many relationship between database and application. Application A will maintain the integrity of its part of the data. When the system becomes larger, application B is added. The two applications A and B may be developed differently. The team does it. How to coordinate to ensure data integrity, and if a new C application is added after a year, how to deal with it?

2. Opposite view

1. Triggers or applications can be used to ensure data integrity

2. Overemphasis or use of primary keys/foreign keys will increase development time Difficulty, leading to problems such as too many tables

3. Data management is simple, easy to operate, and high performance when no foreign keys are used (operations such as import and export are faster when inserting, updating, and deleting data)

Don’t even think about foreign keys in a massive database. Just imagine, a program needs to insert millions of records every day. When there are foreign key constraints, it needs to scan whether the record is qualified every time. Usually it is more than A field has a foreign key, so the number of scans increases exponentially! One of my programs was completed in 3 hours. If foreign keys were added, it would take 28 hours!

3. Conclusion

1. In large systems (low performance requirements, high security requirements), use foreign keys; in large systems (high performance requirements, security requirements are controlled by yourself), No foreign keys are needed; for small systems, it is better to use foreign keys.

2. Use foreign keys appropriately and not pursue them excessively

In order to ensure the consistency and integrity of the data, you can control it through programs without using foreign keys. At this time, a layer should be written to implement data protection, and then various applications of the database can be accessed through this layer.

It should be noted that:

MySQL allows the use of foreign keys, but for integrity checking purposes, this feature is ignored in all table types except the InnoDB table type. This may seem weird, but it's actually quite normal: performing an integrity check after every insert, update, and delete of all foreign keys in a database is a time- and resource-consuming process that can impact performance, especially when processing complex of or the number of winding connections. Therefore, users can choose the one that suits their specific needs on the basis of the table.

So, if you need better performance and don't need integrity checking, you can choose to use the MyISAM table type. If you want to build a table based on referential integrity in MySQL and want to maintain good performance on this basis. For better performance, it is best to choose the table structure as innoDB type

The above is the detailed content of What are the constraints of MySQL?. For more information, please follow other related articles on the PHP Chinese website!