A foreign key constraint fails on production but not on localhost-CodePudding

I have a simple CRUD that i built and it works perfectly on localhost, but for some reason it keeps on insisting throwing errors when on production.

Posts table is -

CREATE TABLE `posts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `title` json NOT NULL,
  `meta_title` json NOT NULL,
  `meta_description` json NOT NULL,
  `slug` json NOT NULL,
  `body` json DEFAULT NULL,
  `published_at` date DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '0',
  `author_id` bigint unsigned DEFAULT NULL,
  `category_id` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `posts_author_id_foreign` (`author_id`),
  KEY `posts_category_id_foreign` (`category_id`),
  CONSTRAINT `posts_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `Authors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `posts_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `Categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Authors table is -

CREATE TABLE `authors` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` json NOT NULL,
  `description` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And the error that im getting while trying to create a new post (once again, only in production env) is -

[2021-09-29 07:13:44] production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`blog`.`posts`, CONSTRAINT `posts_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `Authors` (`id`) ON DELETE CASCADE) (SQL: insert into `posts` (`title`, `meta_title`, `meta_description`, `slug`, `body`, `published_at`, `enabled`, `author_id`, `category_id`, `updated_at`, `created_at`) values ({"en":"test","es":"test"}, {"en":"test","es":"test"}, {"en":"test","es":"test"}, {"en":"testtt","es":"test"}, {"en":"<p>asd<\/p>","es":"<p>dsdas<\/p>"}, ?, 0, 8, 5, 2021-09-29 07:13:44, 2021-09-29 07:13:44)) {"userId":1,"exception":"[object] (Illuminate\\Database\\QueryException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`blog`.`posts`, CONSTRAINT `posts_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `Authors` (`id`) ON DELETE CASCADE) (SQL: insert into `posts` (`title`, `meta_title`, `meta_description`, `slug`, `body`, `published_at`, `enabled`, `author_id`, `category_id`, `updated_at`, `created_at`) values ({\"en\":\"test\",\"es\":\"test\"}, {\"en\":\"test\",\"es\":\"test\"}, {\"en\":\"test\",\"es\":\"test\"}, {\"en\":\"testtt\",\"es\":\"test\"}, {\"en\":\"<p>asd<\\/p>\",\"es\":\"<p>dsdas<\\/p>\"}, ?, 0, 8, 5, 2021-09-29 07:13:44, 2021-09-29 07:13:44)) at /var/www/html/blog/vendor/laravel/framework/src/Illuminate/Database/Connection.php:692)

CodePudding user response：

Different versions of MySQL, at a guess. In both of your constraints you reference the table name with a capital letter.

CONSTRAINT `posts_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `Authors` (`id`) ON DELETE CASCADE,
CONSTRAINT `posts_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `Categories` (`id`) ON DELETE CASCADE

MySQL 8.0 can deal with that, MySQL5.7 can not (going by tests on my own localhost and production setups).