Ошибка 3819 mysql

Hello everyone, in this little post we will review a new feature in MySQL 8.

What is “CHECK Constraint”?

This is a new feature to specify a condition to check the value before INSERT or UPDATE into a row. The constraint could return an error if the result of a search condition is FALSE for any row of the table (but not if the result is UNKNOWN or TRUE).

This feature starts working on MySQL 8.0.16, and in previous versions, we could create it, but it doesn’t work, meaning the syntax is supported but it is not working,

There are some rules to keep in mind…

– AUTO_INCREMENT columns are not permitted
– Refer to another column in another table is not permitted
– Stored functions and user-defined functions are not permitted (you can not call a function or any user-defined functions)
– Stored procedure and function parameters are not permitted (you cannot call a procedure and function parameters)
– Subqueries are not permitted
– Columns used in foreign key for the next actions (ON UPDATE, ON DELETE) are not permitted
– This CHECK is evaluated for the next statements INSERT, UPDATE, REPLACE, LOAD DATA, and LOAD XML. Also, CHECK constraint is evaluated for INSERT IGNORE, UPDATE IGNORE, LOAD DATA … IGNORE, and LOAD XML … IGNORE. For those statements, a warning occurs if a constraint evaluates to FALSE. The insert or update is skipped.

Let’s See Some Examples

I created the next table to test this functionality. This is super easy as you can see in examples:

In this simple test, we can write or update rows only if the “age” column value is more than 15.

Let’s see an example trying to INSERT rows with the “age” column less than 15:

To DROP, use the next example:

Let’s see another example adding more logic into it. I altered the table with the next CHECKs:

We added more logic, and now it depends on the “gender” and “age” columns. A CHECK constraint is satisfied if, and only if, the specified condition evaluates to TRUE or UNKNOWN(for NULL column value) for the row of the table. The constraint is violated otherwise.

Let see an example from the previous logic.

As you can see in the ERROR message, MySQL is showing the CHECK constraint name. This is good to use from the application source code to debug the error and to know from which CHECK is failing.

Finally, this is the table structure:

We can add more logic in the table using this feature, but from my previous experience as a programmer, I don’t recommend adding logic in the tables because it is difficult to find or debug errors unless you do not have access to the application code.

Introduction

Starting from MySQL 8.0.16 database server and later versions, a CHECK constraint is a feature that allows you to specify a condition that validates data during an INSERT or UPDATE command. If the constraint is violated when an SQL statement is issued against a table, MySQL raises a general error and aborts the operation altogether. You can use the MySQL CHECK constraints to enforce business logic at the backend of your application to achieve data integrity. Also, by defining specific data restrictions in the database server, you can be sure that all data administrators in a multi-user environment adhere to the set business rules when making modifications directly in the database without going through a user interface.

For instance, in your organization, you can place a policy that requires all employees to come from the state of CALIFORNIA. To avoid this rule from being violated in the system, you can code a validation logic in your database. Any database administrator registering employees from the non-allowlisted states should get an error. To put this in a better perspective, you’ll create a sample_company database and an employees table in this guide. You’ll then put a CHECK constraint to limit the value range that can be placed in the state column for new staff members joining your hypothetical company.

Prerequisites

To follow along with this tutorial, make sure you have the following:

• An Vultr Ubuntu 20.04 server.

• A sudo user.

• A LAMP Stack. You can also follow this tutorial on a Vultr One-Click LAMP server.

Create a sample_company Database

Connect to your server and log in to MySQL as root.

$ sudo mysql -u root -p

Enter the root password of your MySQL server and press ENTER to proceed. Then, issue the command below to create a sample_company database`.

mysql> CREATE DATABASE sample_company;

Switch to the sample_company database.

mysql> USE sample_company;

Create an employees Table

You’ll create an employees table. This table acts as an employee register, and new staff members’ details must be inserted here. The employee_id will be a unique key to identify each employee. Then, the first_name and last_name fields will record the full names of the employees. Finally, you’ll use the state column to record the employees’ localities.

Run the command below to create the employees table.

mysql> CREATE TABLE employees (

       employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

       first_name VARCHAR(50),

       last_name VARCHAR(50),

       state  VARCHAR(50)

       ) ENGINE = InnoDB;

Before entering any records into the table, you’ll set a validation rule in the next step.

Set a Check Constraint in the employees Table

With the employees table in place, you’re going to set a CHECK constraint that only allows staff members from the CALIFORNIA state.

Run the command below to add the constraint.

mysql> ALTER TABLE employees

       ADD CHECK (state = 'CALIFORNIA');

Make sure you get the output below to confirm the table has been updated with a new constraint.

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

Test the CHECK Constraint

Create a valid record in the employees table. For this entry, don’t violate the state column’s CHECK constraint.

mysql> INSERT INTO employees

       (

       first_name,

       last_name,

       state

       )

       VALUES

       (

       'JOHN',

       'DOE',

       'CALIFORNIA'

       );

Your record should be inserted into the employees table without any problems.

Query OK, 1 row affected (0.00 sec)

Query the employees table to confirm if the record was inserted into the table.

mysql> SELECT

       employee_id,

       first_name,

       last_name,

       state

       FROM employees;

The output below confirms that the record is in place.

+-------------+------------+-----------+------------+

| employee_id | first_name | last_name | state      |

+-------------+------------+-----------+------------+

|           1 | JOHN       | DOE       | CALIFORNIA |

+-------------+------------+-----------+------------+

1 row in set (0.00 sec)

Next, attempt to insert a new employee from the FLORIDA state and see if the CHECK constraint for the state column will be violated.

Run the INSERT command below.

mysql> INSERT INTO employees

       (

       first_name,

       last_name,

       state

       )

       VALUES

       (

       'MARY',

       'SMITH',

       'FLORIDA'

       );

The command above should fail, and you should receive a standard error message displaying that the constraint has been violated.

ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

Map Multiple Constraints

For this demonstration, you may use the MySQL IN clause to create an allowlist of multiple states that you want to be accepted in the employees table.

To do this, you’ll need to delete the first CHECK constraint and create a new one. Confirm the name of the constraint by running the SELECT command below against the INFORMATION_SCHEMA table.

mysql> SELECT

       CONSTRAINT_NAME,

       TABLE_SCHEMA,

       TABLE_NAME

       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

       WHERE TABLE_NAME = 'employees'

       AND CONSTRAINT_SCHEMA = 'sample_company'

       AND  CONSTRAINT_TYPE = 'CHECK';

Check the name of the constraint as displayed in the CONSTRAINT_NAME column as shown below.

+-----------------+----------------+------------+

| CONSTRAINT_NAME | TABLE_SCHEMA   | TABLE_NAME |

+-----------------+----------------+------------+

| employees_chk_1 | sample_company | employees  |

+-----------------+----------------+------------+

1 row in set (0.01 sec)

Then, DROP the constraint. In this case, delete the employees_chk_1.

mysql> ALTER TABLE employees

       DROP CHECK  employees_chk_1;

The output below confirms the deletion of the employees_chk_1 constraint.

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

Create a new constraint with the new rules.

mysql> ALTER TABLE employees

       ADD CHECK (state IN ('CALIFORNIA', 'COLORADO', 'ARIZONA'));

Ensure you get the output as shown below.

Query OK, 1 row affected (0.23 sec)

Records: 1  Duplicates: 0  Warnings: 0

Next, try to insert new records from both the COLORADO and ARIZONA state.

mysql> INSERT INTO employees

       (

       first_name,

       last_name,

       state

       )

       VALUES

       (

       'JANE',

       'MARK',

       'COLORADO'

       );



mysql> INSERT INTO employees

       (

       first_name,

       last_name,

       state

       )

       VALUES

       (

       'PETER',

       'SMITH',

       'ARIZONA'

       );

You should get a success message from both INSERT statements above.

 Query OK, 1 row affected (0.00 sec)

Confirm the data by running a SELECT statement against the employees table.

mysql> SELECT

       employee_id,

       first_name,

       last_name,

       state

       FROM employees;

You should now see a list of 3 employees as shown below.

+-------------+------------+-----------+------------+

| employee_id | first_name | last_name | state      |

+-------------+------------+-----------+------------+

|           1 | JOHN       | DOE       | CALIFORNIA |

|           2 | JANE       | MARK      | COLORADO   |

|           3 | PETER      | SMITH     | ARIZONA    |

+-------------+------------+-----------+------------+

3 rows in set (0.00 sec)

Again, try to violate the CHECK constraint with an employee from a non-allowlisted state such as HAWAII) and see if an error will be thrown.

mysql> INSERT INTO employees

       (

       first_name,

       last_name,

       state

       )

       VALUES

       (

       'FRANK',

       'JACOB',

       'HAWAII'

       );

You should get an error as shown below because FRANK JACOB does not come from CALIFORNIA, COLORADO or ARIZONA.

ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

This confirms that your CHECK constraint has indeed mapped multiple allowlist values and is working as expected.

Conclusion

In this tutorial, you’ve used MySQL CHECK constraint to improve your MySQL database’s data integrity. You should always take advantage of this feature to enforce data quality and improve your business processes’ logic.

As with any application, the correctness of the data is required. For example, the user’s age must be greater than zero, the user’s login name must not contain spaces, the user’s password must meet a certain complexity, and so on.

For these requirements, although we can validate the data entered by the user in the application interface, this cannot replace the data validation at the database level. This can increase the security of the application.

MySQL provides CHECK constraints to ensure that the data stored in the table meets your requirements. Data that does not meet the CHECK constraints will be rejected.

Note that MySQL didn’t really support CHECK constraints until MySQL 8.0.16. In earlier versions, you could only simulate CHECK constraints through triggers or views with WITH CHECK OPTION.

MySQL CHECK syntax

The following is the syntax of MySQL CHECK:

Here, expr is a boolean expression that evaluates to a row of data. If it returns true, then MySQL allows this row to be inserted into the table, otherwise MySQL rejects this row into the table with an error.

You can create CHECK constraints in a CREATE TABLE statement or add CHECK constraints in a ALTER TABLEstatement statement.

If you use a CHECK constraint in a column definition, the CHECK expression can only refer to this column.

If you are using stand-alone CHECK constraints, the CHECK expression can be applied to all columns on the table.

The following takes the age column needs to be greater than 0 as an example, and uses a different method to add this constraint:

• Use CHECK constraints in column definition in a CREATE TABLE statement

  CREATE TABLE user (
      id INT AUTO_INCREMENT PRIMARY KEY,
      age INT NOT NULL
  );
  

• Use CHECK constraints in the table level in a CREATE TABLE statement

  CREATE TABLE user (
      id INT AUTO_INCREMENT PRIMARY KEY,
      age INT NOT NULL,
      CONSTRAINT CHECK(age > 0)
  );
  

• Add CHECK Constraint in a ALTER TABLE statement

ALTER TABLE user
ADD CONSTRAINT CHECK(age > 0);

MySQL CHECK Constraint Examples

Through the following examples, you will easily understand the usage and role of MySQL CHECK constraints.

Suppose, you need a table to store the user’s name, login name, password, and need to meet the following requirements:

1. The user’s name cannot be null.
2. The login name must be at least 4 characters.
3. The password must be at least 8 characters.
4. The password cannot be the same as the login name.

You can create a table using the following CREATE TABLE statement:

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(45) NOT NULL,
    login_name VARCHAR(45) NOT NULL CHECK(length(login_name) >= 4),
    password VARCHAR(45) NOT NULL CHECK(length(password) >= 8),
    CONSTRAINT CHECK(login_name <> password)
);

Here, there are 3 CHECK constraints in the CREATE TABLE statement:

1. In the login_name column definition, CHECK(length(login_name) >= 4) ensures that the length of the login name is not less than 4.
2. In the password column definition, CHECK(length(password) >= 8) ensures that the length of the login name is not less than 8.
3. The constraint CONSTRAINT CHECK(login_name <> password) on the table ensures that password cannot be the same as the login name.

You can view constraints on the user table with the following SHOW CREATE TABLE statement:

*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `login_name` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_chk_1` CHECK ((length(`login_name`) >= 4)),
  CONSTRAINT `user_chk_2` CHECK ((length(`password`) >= 8)),
  CONSTRAINT `user_chk_3` CHECK ((`login_name` <> `password`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As you can see in the output above, there are 3 CHECK constraints. Constraint names are generated by MySQL by default.

Note that the name column’s NOT NULL is also a special constraint.

To verify that whether the CHECK(length(login_name) >= 4) constraint is in effect, try to insert a row using the following INSERT statement:

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'tim', 'timisok');

Since tim is less than 4, MySQL gives the following error:

ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.

To verify that whether the CHECK(length(password) >= 8) constraint is in effect, try inserting a row using the following INSERT statement:

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'tim1', 'timisok');

Since timisok is less than 8, MySQL gives the following error:

ERROR 3819 (HY000): Check constraint 'user_chk_2' is violated.

To verify that whether the CONSTRAINT CHECK(login_name <> password) constraint is in effect, try inserting a row using the following INSERT statement:

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'timisgood', 'timisgood');

Since the login and password given in the above statement are both timisgood, MySQL gives the following error:

ERROR 3819 (HY000): Check constraint 'user_chk_3' is violated.

You can use the following statement to insert a row that meets all the CHECK constraints.

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'hitim', 'timisgood');

The row was successfully inserted into the user table.

Conclusion

MySQL provides CHECK constraints to ensure that the data stored in the table meets your requirements. Rows that do not meet the CHECK constraints are rejected for insertion into the table.

You can add CHECK constraints for a column or a table.

Earlier I wrote about check constraints when MySQL 8.0.16 was released. But this week I noticed two different folks having similar problems with them. And sadly it is ‘pilot error’.

The first was labeled  MYSQL 8.0.17 CHECK not working even though it has been implemented and a cursory glance may make one wonder what is going on with the database.

The table is set up with two constraints. And old timers will probably mutter something under their breath about using ENUMs but here they are:

 JOB_TITLE varchar(20) CHECK(JOB_TITLE IN (‘Lecturer’, ‘Professor’, ‘Asst. Professor’, ‘Sr. Lecturer’)),  

DEPARTMENT varchar(20) CHECK(DEPARTMENT IN (‘Biotechnology’, ‘Computer Science’, ‘Nano Technology’, ‘Information Technology’)), 

And if you cut-n-paste the table definition into MySQL Workbench or MySQL Shell, it is perfectly valid DDL. 

So the table is good. 

check constraint means to filter one or more columns according to certain preset rules. If the condition is true, the filtering succeeds; if the condition is false, the filtering fails and the failure code is returned to the client.

Why do you want to list and write this separately? You often encounter irregular SQL. In many cases, you can only filter by the database layer. If you don’t filter on the code side, you can only filter on the database side.

1, Common filtering methods

Assume table f1, field r1 is a multiple of 3, otherwise write is rejected. And suppose that the input of r1 is not standardized and can only be filtered by the database, what should we do? There are only a few:

1) Write pre trigger

Example 1

mysql> create table f1 (r1 int);
Query OK, 0 rows affected (0.03 sec)

DELIMITER $

USE `ytt`$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_check_f1_r1`$

CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `tr_check_f1_r1` BEFORE INSERT ON `f1
FOR EACH ROW BEGIN
IF MOD(new.r1,3) <> 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Column r1 should be mod by 3,failed to insert.';
END IF;
END;
$
DELIMITER ;

Under execution, exceptions are exposed

mysql> insert into f1 values (5);
ERROR 1644 (45000): Column r1 should be mod by 3,failed to insert.

Normal insertion

mysql> insert into f1 values (3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from f1;
+------+
| r1   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

The above example is simple for the scenario of single column filtering, and the complex filtering of multiple columns will be discussed later.

2) Write stored procedure encapsulate SQL

Processing input constraint in stored procedure is the same as processing input constraint logic in program side, but the same processing logic is placed in database side, and all data input in the future can only rely on a single entry of stored procedure.

3) Do not reject any input, and regularly process irregular data

This will lead to a large amount of data input, and there are many useless irregular data. Generally, irregular data is processed regularly during non business peak hours.

There are no examples of these two methods, which are similar to the first one.

2, CHECK constraint

Now we want to talk about the CHECK constraint that has been standardized before the table based definition filtered at the column level. (MySQL version > = 8.0.16)

mysql> create table f1 (r1 int constraint tb_f1_r1_chk1 check (mod(r1,3)=0));
Query OK, 0 rows affected (0.03 sec)

mysql> create table f2 (r1 int constraint tb_f2_r1_chk1 check (mod(r1,3)=0) not enforced);
Query OK, 0 rows affected (0.02 sec)

Here, the related restrictions of CHECK constraint are as follows:

1. constraint names are unique in each database.

In other words, there are not two identical constraints in a single database. If they are not defined, the system will automatically generate a unique constraint name.

1. The check constraint is valid for the statement insert/update/replace/load data/load xml; it is invalid for the corresponding ignore statement.

2. Not every function can be used. For example, if the result of the function is uncertain: NOW(), connection ﹣ ID (), current ﹣ user().

3. Not for stored procedures and stored functions.

4. System variable is not applicable.

5. Subqueries are not applicable.

6. Foreign key actions (such as ON UPDATE, ON DELETE) are not applicable.

7. Enforced is enabled by default. If not enforced is added separately, the check constraint fails.

Example 2

In combination with the actual examples of the two tables above, the check constraint is only valid for table f1.

mysql> insert into f1 values (10);
ERROR 3819 (HY000): Check constraint 'tb_f1_r1_chk1' is violated.
mysql> insert into f2 values (10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from f1
Empty set (0.00 sec)

mysql> select  * from f2;
+------+
| r1   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

Let’s look at a more detailed example of the CHECK constraint.

Example 3

mysql> drop table f1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table f1
-> (
->  r1 int constraint tb_f1_r1_chk1 check (r1 > 10),
->  r2 int constraint tb_f1_r2_positive check (r2 > 0),
->  r3 int constraint tb_f1_r3_chk1 check (r3 < 100),
->  constraint tb_f1_r1_nonzero check (r1 <> 0),
->  constraint tb_f1_r1r2_chk1 check (r1 <> r2),
->  constraint tb_f1_r1r3_chk1 check (r1 > r3)
-> );
Query OK, 0 rows affected (0.02 sec)

There is a point in the above example,

1. The constraints TB ﹣ F1 ﹣ R1 ﹣ nonzero, TB ﹣ F1 ﹣ R1 ﹣ R2 ﹣ CHK1, TB ﹣ F1 ﹣ R3 ﹣ chk do not follow the fixed column, which is effective for the whole, or the table based check constraint.

2. Constraint TB ﹣ F1 ﹣ R1 ﹣ contains constraint TB ﹣ F1 ﹣ R1 ﹣ nonezero, so that TB ﹣ F1 ﹣ R1 ﹣ nonezero will never detect an exception. So after checking, remove this constraint.

The definition after removing redundant constraints,

mysql> create table f1
-> (
->  r1 int constraint tb_f1_r1_chk1 check (r1 > 10),
->  r2 int constraint tb_f1_r2_positive check (r2 > 0),
->  r3 int constraint tb_f1_r3_chk1 check (r3 < 100),
->  constraint tb_f1_r1r2_chk1 check (r1 <> r2),
->  constraint tb_f1_r1r3_chk1 check (r1 > r3)
-> );
Query OK, 0 rows affected (0.02 sec)

If you want to do a test on this table, you can see that the constraint of each column here is actually the relationship between and. If any column constraint is not established, the write fails.

mysql> insert into f1 values (20,10,10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into f1 values (10,10,10);
ERROR 3819 (HY000): Check constraint 'tb_f1_r1_chk1' is violated.
mysql> insert into f1 values (20,-10,10);
ERROR 3819 (HY000): Check constraint 'tb_f1_r2_positive' is violated.
mysql> insert into f1 values (20,10,30);
ERROR 3819 (HY000): Check constraint 'tb_f1_r1r3_chk1' is violated.

Next, we modify the trigger at the beginning. As long as we add the relevant conditions, we can implement the same check column constraint.

DELIMITER $

USE `ytt`$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_check_f1_r1`$

CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `tr_check_f1_r1` BEFORE INSERT ON `f1`
FOR EACH ROW BEGIN
DECLARE v1 TINYINT DEFAULT 0;

IF (new.r1 > 10 AND new.r1 > new.r3 AND new.r1 <> new.r2 AND new.r2 > 0 AND new.r3 < 100) = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Failed to write: constraint check: n (n r1 >10 n&&  r1 > r3 n&& r1 <> r2 n&& r2> 0 n&& r3 < 100n).";
END IF;
END;
$

DELIMITER ;

Test results,

mysql> insert into f1 values (20,30,100);
ERROR 1644 (45000): Failed to write: constraint check:
(
r1 >10
&&  r1 > r3
&& r1 <> r2
&& r2> 0
&& r3 < 100
).

mysql> insert into f1 values (100,30,90);
Query OK, 1 row affected (0.01 sec)

mysql> select * from f1;
+------+------+------+
| r1   | r2   | r3   |
+------+------+------+
|  100 |   30 |   90 |
+------+------+------+
1 row in set (0.00 sec)

conclusion

This paper introduces the usage of database CHECK constraint and some examples.

I personally suggest that the best way to implement this CHECK constraint is to split it from the database side to the application side. The simpler the data side, the better the performance. But there are exceptions. If the application side is difficult to implement due to historical reasons or other factors, it can only be sent to the database side.

What else do you want to know about MySQL technology? Leave a message and tell Xiaobian!