Foreign key constraint fails but referenced row exists

Question

I'm running MySQL 5.7.21 on Amazon RDS.

I know this question has been asked a thousand times, but I'm getting the issue on a scenario I wouldn't expect, so please read through before downvoting or marking as duplicate.

I'm not restoring the database, just running single INSERT queries, so is not a matter of ordering.
The referenced row does exist on the table; me and my colleagues had it triple checked.
As one might expect, disabling the FK checks with SET foreign_key_checks = 0 does make the query work.
I've seen this happening because of different table charsets, but in this case, both use utf8mb4. Also both have collation set to utf8mb4_general_ci.
This is happening in a production environment, so dropping the tables and recreating them is something I would like to avoid.

Some additional information:

The FK constraint was created AFTER the original tables were already populated.

Here is the relevant portion of the current DDL:

CREATE TABLE `VehicleTickets` (
  `id` varchar(50) NOT NULL,
  `vehiclePlate` char(7) NOT NULL,
  `organizationId` varchar(50) NOT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NULL DEFAULT NULL,
  `status` varchar(15) NOT NULL DEFAULT 'OPEN',
  `description` text NULL DEFAULT NULL,
  `ticketInfo` json DEFAULT NULL,
  `externalId` varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract(`ticketInfo`,'$.externalId'))) VIRTUAL,
  `value` decimal(10,2) GENERATED ALWAYS AS (json_unquote(json_extract(`ticketInfo`,'$.value'))) VIRTUAL,
  `issuedAt` timestamp GENERATED ALWAYS AS (json_unquote(json_extract(`ticketInfo`,'$.issuedAt'))) VIRTUAL NOT NULL,
  `expiresAt` timestamp GENERATED ALWAYS AS (json_unquote(json_extract(`ticketInfo`,'$.expiresAt'))) VIRTUAL NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `VehicleTickets_externalId_unq_idx` (`externalId`,`organizationId`),
  KEY `VehicleTickets_vehiclePlate_idx` (`vehiclePlate`),
  KEY `VehicleTickets_organizationId_idx` (`organizationId`),
  KEY `VehicleTickets_issuedAt_idx` (`createdAt`),
  KEY `VehicleTickets_expiresAt_idx` (`expiresAt`),
  CONSTRAINT `VehicleTickets_Organizations_fk` 
      FOREIGN KEY (`organizationId`) REFERENCES `Organizations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Organizations` (
  `id` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `taxPayerId` varchar(50) DEFAULT NULL,
  `businessName` varchar(100) DEFAULT NULL,
  `status` varchar(15) NOT NULL DEFAULT 'TESTING',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `activatedAt` timestamp NULL DEFAULT NULL,
  `assetConfiguration` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When I run:

select * from VehicleTickets where organizationId not in (
    select id from Organizations
);

I get an empty result set.

However, if I run a query like this:

 insert into `VehicleTickets` (
  `id`,
  `createdAt`,
  `organizationId`,
  `ticketInfo`,
  `vehiclePlate`
 )
    values (
      '... application generated id',
      '... current date ',
      'cjlchoksi01r8nfks3f51kht8', -- DOES EXIST on Organizations
      '{ ... some JSON payload }',
      '... vehicle plate'
    )

This produces the following error:

Cannot add or update a child row: a foreign key constraint fails (VehicleTickets, CONSTRAINT VehicleTickets_Organizations_fk FOREIGN KEY (organizationId) REFERENCES Organizations (id))

Additionally, it gives me:

"errno": 1452,
"sqlState": "23000",

I've read through several threads regarding this issue, but couldn't find a similar case.

Foreign key constraint fails but referenced row exists

0 Answers