preface

A constraint literally means to specify or restrict how something should be done. In MySQL, a constraint is to specify rules for data in a table, that is, to restrict data to ensure reliability, such as not allowing Null values in a column.

  • NOT NULL: Ensures that columns cannot have NULL values
  • CHECK: Ensures that the values in the column meet certain conditions
  • UNIQUE: Make sure that all values in a column are different
  • PRIMARY KEY:NOT NULLandUNIQUECombination that uniquely identifies each row in the table
  • FOREIGN KEY: Foreign key constraint
  • DEFAULT: Sets a default value for the column if no value is specified

The constraint

1.NULL

MySQL > create table with NOT NULL;

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

Copy the code

If you try to insert a null value, an exception is thrown.

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

Or add a NOT NULL constraint to an 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 the code

Delete the NOT NULL constraint.

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

Copy the code

2.CHECK

If you want to define a constraint on the column, you can use CHECK, such as below, to force the age field to be greater than 18 and less than 80, otherwise 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 the code

Insert test, it can be found that 9, 81 throw an exception when insert.

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> 
Copy the code

Multiple column constraints can also be applied, such as age must be greater than 18 and city must be In China.

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

Insert tests.

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.'China');
Query OK, 1 row affected (0.01 sec)

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

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

Copy the code

You can also make the column value be in the specified set, such as gender must be in male, female, unknown, and ladyboy set.

mysql> create table user(sex varchar(255) check (sex in ('male'.'woman'.'unknown'.'人妖')));
Query OK, 0 rows affected (0.05 sec)
Copy the code

Insert tests.

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

mysql> insert user values(" men choose men "); ERROR3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(" female "); Query OK,1 row affected (0.01 sec)

mysql> insert user values(" we "); Query OK,1 row affected (0.00 sec)
Copy the code

Name and delete the constraints.

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: 0

mysql> insert user values(5);
Query OK, 1 row affected (0.01 sec)
Copy the code

But have you ever seen it written like this?

Guess what the following action is.

This is a case when condition that only inserts numbers >=18, or numbers between 0 and 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 the code

3.UNIQUE

The UNIQUE constraint ensures that there are no duplicate values in the column. Both UNIQUE and PRIMARY KEY constraints guarantee the uniqueness of a column value, but UNIQUE can occur multiple times per table, whereas PRIMARY KEY can occur only once.

For example, the name field cannot be repeated.

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

Copy the code

Insert tests.

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

mysql> insert user values(" * * "); ERROR1062 (23000): Duplicate entry 'Joe' for key 'user.name'
mysql> 
Copy the code

Name the 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(" * * "); ERROR1062 (23000): Duplicate entry 'Joe' 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)

Copy the code

After insertion, you can view the create statement with the following 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 the code

To DROP the UNIQUE constraint, 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 the code

Add to an existing table.

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 the code

4.PRIMARY KEY

Typically, each table contains a value that uniquely identifies each row. This column is called a 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> 

Copy the code

5.FOREIGN KEY

A FOREIGN KEY is used to constrain that a column in a table must be a value that exists in a field in another table, but in another table that column must not be a primary KEY but must be a unique index, or the creation will fail.

For example, userids in the Orders table must refer to ids in the User table, and cannot be inserted if the inserted userId does not exist in the User table.

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 the code

But there is a problem. What happens to the records in Orders if the records in the main table (User) are deleted or updated? In the following example, you can find a direct error.

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 the code

MySQL provides several constraints to help us solve this problem, such as orders being updated when the USER table is updated.

  1. RESTRICT: Refuses to update or delete records in the parent table if there are records in the child table.

  2. CASCADE: Automatically updates or deletes records in child tables when updating or deleting records in parent tables.

  3. SET NULL: Sets the value of a field in a child table to NULL when a parent table record is updated or deleted.

RESTRICT is set to RESTRICT by default, and null is set to RESTRICT when dropped.

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 the code

Test the update

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 |Zhang SAN|
+----+--------+
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 the code

Test delete.

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 the code

6.DEFAULT

The DEFAULT constraint is used to set the DEFAULT value for a column. If no value is assigned to a column, the system automatically inserts a DEFAULT value for that column. No assignment means that the column is not specified when the insert inserts data.

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

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

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