error in making Foreign Key Constraints by Workbench

Question

I made database consists of two columns in MySQL Workbrench. I imported data from .csv to sql. The sctructures of columns are: for table 'gases'

CREATE TABLE `gases` (
  `site_gaw_id` text,
  `year` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `value` double DEFAULT NULL,
  `ID_station` int(11) NOT NULL,
  `name_of_value` text,
  KEY `ID_station` (`ID_station`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

for table stations_2

CREATE TABLE `stations_2` (
  `ID_stations` int(11) NOT NULL,
  `Name` text,
  `Type` text,
  `Country` text,
  `Latitude` double DEFAULT NULL,
  `Longitude` double DEFAULT NULL,
  `elevation` int(11) DEFAULT NULL,
  `site_gaw_id` text,
  `stations_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID_stations`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

I want to make relations in workbrench between two columns.The relation should be by by "ID_station".

So I need to make Foreign Key Constraints. In workbrench I write:

ALTER TABLE `gases_db`.`gases` 
ADD CONSTRAINT `id_station_fk`
  FOREIGN KEY (`ID_station`)
  REFERENCES `gases_db`.`stations_2` (`ID_stations`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

And as the result I got an error:

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`gases_db`.`#sql-3b5_a`, CONSTRAINT `id_station_fk` FOREIGN KEY (`ID_station`) REFERENCES `stations_2` (`ID_stations`) ON DELETE CASCADE ON UPDATE CASCADE)
SQL Statement:

How should I solve my problem?

Answer 1

There are rows in the table that fail the foreign key validation. That is, you have some id_station in gases that cannot be found in column id_stations of table stations_2.

You can exhibit the offending rows with the following query:

select *
from gases g
where not exists (select 1 from stations_2 s where s.id_stations = g.id_station)

You need to fix this wrong data in table gases (either by updating id_station to a valid value, or by removing the row). When the query returns no rows, then you know that the foreign key can be created.