An in-depth analysis of the 6 common constraint types in MySQL-Mysql Tutorial-php.cn

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)

Copy after login

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

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

Copy after login

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

Copy after login

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

Copy after login

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)

Copy after login

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

mysql> insert user values(9);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; 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 &#39;user_chk_1&#39; is violated.
mysql>

Copy after login

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=&#39;中国&#39;));
Query OK, 0 rows affected, 1 warning (0.05 sec)

Copy after login

Insert test.

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

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

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

Copy after login

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 (&#39;男&#39;,&#39;女&#39;,&#39;未知&#39;,&#39;人妖&#39;)));
Query OK, 0 rows affected (0.05 sec)

Copy after login

Insert test.

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

mysql> insert user values("男男");
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; 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)

Copy after login

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 &#39;CHK_AGE&#39; 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)

Copy after login

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

Copy after login

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)

Copy after login

Insert test.

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

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

Copy after login

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 &#39;张三&#39; for key &#39;user.name_un&#39;
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)

Copy after login

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)

Copy after login

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)

Copy after login

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

Copy after login

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 &#39;1&#39; for key &#39;user.PRIMARY&#39;mysql>

Copy after login

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)

Copy after login

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`)

Copy after login

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.

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

Copy after login

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)

Copy after login

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)

Copy after login

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.

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

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

mysql> select * from user;
+------+
| age  |
+------+
|   18 |
+------+
1 row in set (0.00 sec)

Copy after login

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!