SQL: "Warning (Code 3819): Check constraint is violated" makes no sense when using LOAD DA-CodePudding

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 '\r\n' 
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.

CodePudding user response：

You don't have the column names in the correct order in the LOAD DATA query.

LOAD DATA LOCAL INFILE "path_to_file_in_my_computer/Teams.csv"  
INTO TABLE test_schema.teams 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES
(yearid, leagueid, teamid, @franchid, @divid, teamrank);

You can assign directly to the table column names, you don't need to use @yearID unless you have to do extra processing before storing in the table.