Ошибка 3819 mysql

I have a .csv file which look like this:

and that can be downloaded from here.

I create my DB and my table with the following code:

CREATE DATABASE test_schema;

CREATE TABLE test_schema.teams (
    teamkey SMALLINT NOT NULL AUTO_INCREMENT, 
    teamid CHAR(3) NOT NULL,
    yearid YEAR(4) NOT NULL,
    leagueid CHAR(2) NOT NULL,
    teamrank TINYINT(2) NOT NULL,
  PRIMARY KEY (teamkey),
  UNIQUE KEY teamkey_UNIQUE (teamkey),
  KEY teamid_yearid_leagueid_UNIQUE (teamid, yearid, leagueid),
CONSTRAINT check_teamrank CHECK (((teamrank >= 0) and (teamrank <= 12))),
CONSTRAINT check_year CHECK (((yearid >= 1871) and (yearid <=2155))))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now, when I try to import using:

LOAD DATA LOCAL INFILE "path_to_file_in_my_computer/Teams.csv"  
INTO TABLE test_schema.teams 
FIELDS TERMINATED BY ',' LINES TERMINATED BY 'rn' 
IGNORE 1 LINES
(@teamID, @yearID, @lgID, @Rank);

I get 2895 warnings which are all the same:

Warning (Code 3819): Check constraint 'check_year' is violated.

This warning makes no sense since yearid goes from 1871 to 2018 as can be corroborated if you look at the structure of the Teams.csv file. So any advice or suggestion on how to handle this error will be much appreciated. I’m working on MySQL Workbench 8.0.

PS: I posted a similar question (deleted) today morning but it needed more details that are provided here.

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.

My question says that it wants me to add a constraint so that the company cannot purchase any more car that were made before 2018.The problem is that my database already has info about cars that were made before 2018. I cant delete the table because it will mess with some of my other queries . Can i get some advice on it?

My code below for vehicle table

create table Vehicle(
LicensePlate varchar(48) primary key,  /*Primary key for table Vehicle for License plate, variable can hold up to 48 characters*/
Make varchar (48),  
CarYear int 
);

insert into Vehicle (LicensePlate,Make,CarYear) values ('1234 AA','Toyota',1970);
insert into Vehicle (LicensePlate,Make,CarYear) values ('1237 AB','Mazda',1995);
insert into Vehicle (LicensePlate,Make,CarYear) values ('1892 BG','Toyota',2000);
insert into Vehicle (LicensePlate,Make,CarYear) values ('1876 FA','Nissan',1999);
insert into Vehicle (LicensePlate,Make,CarYear) values ('3021 AA','Mazda',1950);
insert into Vehicle (LicensePlate,Make,CarYear) values ('2134 FF','Toyota',1992);

-- Company has decided not to purchase any more car that were made before 2018. Add appropriate constraints to the Vehicle table to enforce this requirement.

alter table Vehicle add constraint checker_1 check (CarYear > 2018);

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.