An in-depth analysis of the 6 common constraint types in MySQL

The literal meaning of constraints is to stipulate or limit how something should be done. In MySQL, constraints are to specify rules for the data in the data table, that is, to limit the data. This is to ensure reliability. For example, Null values ​​are not allowed to appear in a certain column. In practice, we will encounter the following types of constraints.

• NOT NULL: Ensure that the column cannot have NULL values
• CHECK: Ensure that the value in the column meets specific conditions
• UNIQUE: Ensures that all values ​​in a column are different
• PRIMARY KEY: NOT NULL and UNIQUE combination , uniquely identifies each row in the table
• FOREIGN KEY: Foreign key constraint
• DEFAULT: If no value is specified, the default is set for the column Value

[Related recommendation: mysql video tutorial]

Constraint

##1.NULL

In MySQL, use

NOT NULL to ensure that Null values ​​will not appear in the columns. When creating the table, the format is as follows:

mysql> create table user(name varchar(255)not null);
Query OK, 0 rows affected (0.06 sec)

If you try to insert a null value, then An exception will be thrown.

mysql> insert user values(null);
ERROR 1048 (23000): Column 'name' cannot be null

Or add a new

NOT NULL constraint on the existing table.

mysql> alter table user modify name varchar(255) not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Delete

NOT NULLConstraint.

mysql> alter table user modify name varchar(255)  null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.CHECK

If you want to define conditional constraints on the column, you can use CHECK, such as the following, to force the age field to be greater than 18 and less than 80, otherwise it will An error will be reported.

mysql> create table user(age int(11) check(age>18 and age <80));
Query OK, 0 rows affected, 1 warning (0.06 sec)

Insertion test, you can find that 9 and 81 throw exceptions when inserting.

mysql> insert user values(9);
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.

mysql> insert user values(19);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(81);
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql>

You can also perform multi-column constraints, such as the age must be greater than 18, and the city must be China.

mysql> create table user(age int(11),city varchar(255) ,check(age>18 and city='中国'));
Query OK, 0 rows affected, 1 warning (0.05 sec)

Insert test.

mysql> insert user values(81,'2');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(8,'2');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(20,'2');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(20,'中国');
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(20,'中国1');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(85,'中国');
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(9,'中国');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.

You can also specify that the column value must be in the specified set, such as gender must be in the male, female, unknown, and transvestite sets.

mysql> create table user(sex varchar(255) check (sex in ('男','女','未知','人妖')));
Query OK, 0 rows affected (0.05 sec)

Insert test.

mysql> insert user values("男");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("男男");
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values("女");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values("人妖");
Query OK, 1 row affected (0.00 sec)

Name the constraint and delete the constraint.

mysql> create table user (age int(11) ,constraint CHK_AGE check(age>18));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values(5);
ERROR 3819 (HY000): Check constraint 'CHK_AGE' is violated.

mysql> alter table user drop check CHK_AGE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert user values(5);
Query OK, 1 row affected (0.01 sec)

But, have you seen this way of writing?

Guess what the following does.

This is actually a case when conditional judgment, so that it can only insert >=18, or a number between 0-10.

CREATE TABLE `user` (`age` int(11) CHECK 
(((case when (`age` >=18) then 1 
else 
(case when age<10 and age >0 then 1 else 2 end) end) =1)));

3.UNIQUE

UNIQUE constraint ensures that there are no duplicate values ​​in the column, UNIQUE and PRIMARY KEY constraints provide guarantee for the uniqueness of a column value, but UNIQUE can appear multiple times in each table, while PRIMARY KEY can only appear once.

The name field below cannot be repeated.

mysql> create table user (name varchar(255),unique(name));
Query OK, 0 rows affected (0.07 sec)

Insert test.

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry '张三' for key 'user.name'mysql>

Name this constraint and delete it.

mysql> create table user (name varchar(255),constraint name_un unique(name));
Query OK, 0 rows affected (0.07 sec)

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry '张三' for key 'user.name_un'
mysql> alter table user drop index name_un;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

After inserting, you can use the following statement to view the creation statement.

mysql> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  UNIQUE KEY `name_un` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

To delete a

UNIQUE constraint, you can use the DROP INDEX or ALTER TABLE statement:

mysql> DROP INDEX name_un ON user;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

in an existing table Add on.

mysql> alter table user add constraint name_un unique(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.PRIMARY KEY

Usually each table contains a value that uniquely identifies each row. This column is called PRIMARY KEY.

mysql> create table user (id int(11) ,age int(11),primary key (id));
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert user values(1,2);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(1,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'mysql>

5.FOREIGN KEY

##FOREIGN KEY

is used to constrain that a field in the table must exist in a field in another table value, but in another table, this column is not necessarily the primary key, but must be a unique index, otherwise the creation will fail. For example, the userId in the orders table must refer to the id in the user table. If the inserted userId does not exist in the user table, it cannot be inserted.

mysql> create table orders (id int(11) primary key ,userId int(11) ,  FOREIGN KEY (userId) REFERENCES user(id) );
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert orders values(1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))

mysql> insert orders values(1,1);
Query OK, 1 row affected (0.01 sec)

But there is a problem. If the records in the main table (user) are deleted or updated, what should happen to the records in orders? , as in the example below, you can find that an error is reported directly.

mysql> update user set id =2 where id =1;

Cannot delete or update a parent row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)

MySQL provides several constraints to help us solve this type of problem. For example, when the user table is updated, orders are also updated one after another.

RESTRICT: Refuse to update or delete records in the parent table if there are records in the child table.
CASCADE: Automatically update or delete records in the child table when updating or deleting records in the parent table.
SET NULL: When updating or deleting parent table records, set the value of the field in the child table to null.
It can be found that RESTRICT is used by default. Let's modify it so that it is also updated when updating, and null is set when deleting.

mysql> alter table orders add constraint orders_ibfk_1  FOREIGN KEY (`userId`) REFERENCES `user` (`id`) on update cascade on
delete set null;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

Test update

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

mysql> insert orders values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> update user set id =2 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |      2 |
+----+--------+
1 row in set (0.01 sec)

Test deletion.

mysql> delete from user where id =2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |   NULL |
+----+--------+
1 row in set (0.00 sec)

6.DEFAULT

DEFAULT

Constraints are used to set default values ​​for columns. If a value is not assigned to a field, the system will automatically Insert a default value for this field. No assignment means that this field is not specified when inserting data. If a null value is specified, the null value will still be stored in the end. <pre class='brush:php;toolbar:false;'>mysql&gt; create table user(age int(11) default 18); Query OK, 0 rows affected, 1 warning (0.05 sec) mysql&gt; insert user values(); Query OK, 1 row affected (0.02 sec) mysql&gt; select * from user; +------+ | age | +------+ | 18 | +------+ 1 row in set (0.00 sec)</pre>

Original address: https://juejin.cn/post/7000352993572814885

Author: i Tingfeng Shiye

More programming related For knowledge, please visit:

programming video

! !

The above is the detailed content of An in-depth analysis of the 6 common constraint types in MySQL. For more information, please follow other related articles on the PHP Chinese website!