Shulou(Shulou.com)05/31 Report--

This article is to share with you about what types of constraints are common in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The literal meaning of a constraint is to specify or restrict how something should be done. In MySQL, a constraint is to specify rules for the data in the data table, that is, to restrict the data, so as to ensure reliability, such as not allowing null values in a column. In practice, we will encounter the following types of constraints.

NOT NULL: ensure that columns cannot have null values

CHECK: ensure that the values in the column meet certain conditions

UNIQUE: make sure that all values in a column are different

PRIMARY KEY: a combination of NOT NULL and UNIQUE that uniquely identifies each row in the table

FOREIGN KEY: foreign key constraint

DEFAULT: if no value is specified, set the default value for the column

Constraint 1.NULL

Use NOT NULL in MySQL to ensure that null values do not appear in the column. The table is created in the following format:

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, an exception is thrown.

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

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

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: 02.CHECK

If you want to define conditional constraints on a column, you can use CHECK, such as below, to force the age field to be greater than 18 and less than 80, or an error will be reported.

Mysql > create table user (age int (11) check (age > 18 and age 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 >

There can also be multiple columns of constraints, such as the age must be greater than 18, and the city must be Chinese.

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

Insert the test.

Mysql > insert user values (81); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.mysql > insert user values (8); ERROR 3819 (HY000): Check constraint' user_chk_1' is violated.mysql > insert user values (20); 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) ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.mysql > insert user values (85 sec); Query OK, 1 row affected (0.01 sec) mysql > insert user values; ERROR 3819 (HY000): Check constraint' user_chk_1' is violated.

You can also make the column values must be in the specified collection, such as gender must be in the male, female, unknown, shemale collection.

Mysql > create table user (sex varchar (255) check (sex in ('male', 'female', 'unknown', 'shemale')); Query OK, 0 rows affected (0.05sec)

Insert the test.

Mysql > insert user values ("male"); Query OK, 1 row affected (0.02 sec) mysql > insert user values ("male"); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.mysql > insert user values ("female"); Query OK, 1 row affected (0.01sec) mysql > insert user values ("shemale"); Query OK, 1 row affected (0.00 sec)

Name 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.01sec)

But have you ever seen this way of writing?

Guess what the next function is.

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

CREATE TABLE `user` (`age` int (11) CHECK (case when (`age` > = 18) then 1 else (case when age0 then 1 else 2 end) end) = 1); 3.UNIQUE

The UNIQUE constraint ensures that there are no duplicate values in the column, and both the UNIQUE and PRIMARY KEY constraints guarantee the uniqueness of a column of values, but UNIQUE can appear multiple times per table, while PRIMARY KEY can only have one.

As shown below, the name field cannot be repeated.

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

Insert the test.

Mysql > insert user values ("Zhang San"); Query OK, 1 row affected (0.02 sec) mysql > insert user values ("Zhang San"); ERROR 1062 (23000): Duplicate entry 'Zhang San' for key 'user.name'mysql >

Name this constraint and delete it.

Mysql > create table user (name varchar, constraint name_un unique (name)); Query OK, 0 rows affected (0.07 sec) mysql > insert user values ("Zhang San"); Query OK, 1 row affected (0.02 sec) mysql > insert user values ("Zhang San"); 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: 0mysql > insert user values " Query OK, 1 row affected (0.02 sec)

After insertion, you can view the creation 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)

To delete a UNIQUE constraint, you can use a 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: 0mysql > show create table user +- -+ | Table | Create Table | + -+ | user | CREATE TABLE `user` (`name` varchar (255th) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +- -+- -+ 1 row in set (0.00 sec)

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: 04.PRIMARY KEY

Usually each table contains a value that uniquely identifies each row, and 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 Mague 2); Query OK, 1 row affected (0.02 sec) mysql > insert user values (1 Mague 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 a table must be a value that exists in a field in another table, but in another table, the column is not necessarily a primary key, but must be a unique index, otherwise 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 (1meme 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 (1ji1); Query OK, 1 row affected (0.01sec)

But there is a problem: what if the records in the main table (user) are deleted or updated? what about the records in orders? As in the following example, it can be found that the report is directly wrong.

Mysql > update user set id = 2 where id = 1bot 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 that can help us solve these problems, such as when the user table is updated, the orders is updated one after another.

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

CASCADE: automatically updates or deletes records in child tables when you update or delete records in the parent table.

SET NULL: when updating or deleting a parent table record, set the value of the field in the child table to empty.

You can find that RESTRICT is used by default. Let's modify it so that it will also be updated when it is updated, and null will be set when it is deleted.

Mysql > alter table orders add constraint orders_ibfk_1 FOREIGN KEY (`userId`) REFERENCES `user` (`id`) on update cascade ondelete 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 | Zhang San | +-+-+ 1 row in set (0.00 sec) mysql > select * from orders;Empty set (0.00 sec) mysql > insert orders values (1Magne1); Query OK, 1 row affected (0.01 sec) mysql > update user set id = 2 where id = 1 Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from orders;+----+-+ | id | userId | +-- +-+ | 1 | 2 | +-- + 1 row in set (0.01 sec)

Test deletion.

Mysql > delete from user where id = 2 userId 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

The DEFAULT constraint is used to set the default value for the column. If no value is assigned to a field, the system automatically inserts the default value for the field. No assignment means that the field is not specified when insert inserts the data. If you specify a null value, you will end up with a null value.

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) Thank you for reading! This is the end of the article on "what are the common types of constraints in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.