1

I am trying to puzzle a very unusual situation with a foreign key constraint failing despite an existing value in referenced table. My tables:

USERS:

CREATE TABLE `users` (
      `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `first_name` VARCHAR(100) NOT NULL,
      `last_name` VARCHAR(100) NOT NULL,
      `email` VARCHAR(30) NOT NULL,
      `address` VARCHAR(100) NOT NULL,
      `phone` VARCHAR(50) NOT NULL,
      `year_of_birth` INT(4) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

POPULATED USERS TABLE: USERS TABLE

USER_ORDERS:

CREATE TABLE `user_orders` (
       `order_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       `user_id` INT(11) UNSIGNED NULL,
       `start_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
       `due_date` DATE NULL,

CONSTRAINT `fk10_orders_books` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
ON UPDATE CASCADE ON DELETE SET NULL                  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

POPULATED USER_ORDERS: USER ORDERS

BOOKS:

CREATE TABLE `books` (
      `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `title` VARCHAR(100) NOT NULL,
      `condition` ENUM('mint', 'new', 'medium', 'poor', 'needs replacement'),
      `date_added` DATE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

POPULATED BOOKS TABLE: BOOKS TABLE

BOOK_ORDERS: many to many with the aforementioned tables

CREATE TABLE `books_orders` (
      `order_id` INT(11) UNSIGNED NOT NULL,
      `book_id` INT(11) UNSIGNED NOT NULL,
UNIQUE KEY(`order_id`, `book_id`),

CONSTRAINT `fk_orders_user_orders` FOREIGN KEY (`order_id`) REFERENCES `user_orders` (`order_id`) 
ON UPDATE CASCADE ON DELETE CASCADE, 

CONSTRAINT `fk_orders_books` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) 
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now, when trying to run the query below

INSERT INTO `books_orders` (`order_id`, `book_id`) VALUES (1, 1);

I am getting

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (library.books_orders, CONSTRAINT fk_orders_user_orders FOREIGN KEY (order_id) REFERENCES user_orders (order_id) ON DELETE CASCADE ON UPDATE CASCADE)

I blamed unique key, removed it, replaced with composite Primary Key, tried to re-seed the parent tables with a different dataset, however, nothing has changed. Where could the problem be?

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Cheshire Katzzze
  • 127
  • 9
  • You need to show us table users as well. – P.Salmon Sep 28 '19 at 11:11
  • Hello @P.Salmon, added the table and a screenshot with populated table – Cheshire Katzzze Sep 28 '19 at 11:20
  • I cannot reproduce your issue I'm afraid.. – P.Salmon Sep 28 '19 at 11:26
  • You mean, it works on your end? – Cheshire Katzzze Sep 28 '19 at 11:33
  • 1
    @CheshireKatzzze looks like the problem is somewhere else. While moving the data (or adding the data into these tables), did you (by any chance) do `SET FOREIGN_KEY_CHECKS = 0;` ? – Madhur Bhaiya Sep 28 '19 at 11:40
  • 1
    Yes (using s simplified version of your data) no problem. Are there any triggers which could throw this error? If you want to add sample data as text either in the question or sqlfiddle I could try that but I don't see it making a difference. – P.Salmon Sep 28 '19 at 11:41
  • 1
    Maybe, the problem is not with this insert; but with the existing data. Check this answer for few tips on how to find the data integrity issue: https://stackoverflow.com/a/53099922 – Madhur Bhaiya Sep 28 '19 at 11:42
  • @MadhurBhaiya I checked SET FOREIGN_KEY_CHECKS = 0, no, it hasn't been used. Tested if this is the issue, looks like it isn't. As for the topic you sent - I've already been there. From my perspective, my code is fully compliant with every specified reason – Cheshire Katzzze Sep 28 '19 at 11:44
  • 1
    @CheshireKatzzze as P Salmon also pointed out, there is nothing wrong with the schema definition as such. Try to reproduce this issue at the fiddle here: https://www.db-fiddle.com – Madhur Bhaiya Sep 28 '19 at 11:47
  • This DB is pretty small, so I just created a new one, with a new config file, and moved the data into it. With the same dataset, everything worked as charm. Probably you are right, there was an issue with the config, as since you and P.Salmon confirmed it, there is nothing wrong with the schema definition – Cheshire Katzzze Sep 28 '19 at 12:00

0 Answers0