MySQL Foreign Key Error Even With Existent References

Question

I have done some searching about this issue before posting here. Here is the issue:

mysql> insert into Buyer values (5594, CURDATE(), 490, 4830);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`oppminer`.`buyer`, CONSTRAINT `FK3D9119337C53076` FOREIGN KEY (`office_id`) REFERENCES `Office` (`id`))

I've confirmed that the referenced row exists:

mysql> select * from Office where id = 490;
+-----+-----------+-----------+-------------+-------------------+
| id  | name      | agency_id | location_id | primaryContact_id |
+-----+-----------+-----------+-------------+-------------------+
| 490 | Top Level |        88 |        7363 |               451 |
+-----+-----------+-----------+-------------+-------------------+
1 row in set (0.00 sec)

Here is the table description:

| Buyer | CREATE TABLE `Buyer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime DEFAULT NULL,
  `office_id` bigint(20) DEFAULT NULL,
  `primaryContact_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK3D9119337C53076` (`office_id`),
  KEY `FK3D911938ADC7080` (`primaryContact_id`),
  CONSTRAINT `FK3D911938ADC7080` FOREIGN KEY (`primaryContact_id`) REFERENCES `Contact` (`id`),
  CONSTRAINT `FK3D9119337C53076` FOREIGN KEY (`office_id`) REFERENCES `Office` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5595 DEFAULT CHARSET=latin1 |
+-------

| Office | CREATE TABLE `Office` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `agency_id` bigint(20) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `primaryContact_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK8C9C2ADC3FBD3FD6` (`agency_id`),
  KEY `FK8C9C2ADCFF56D2BF` (`location_id`),
  KEY `FK8C9C2ADC8ADC7080` (`primaryContact_id`),
  CONSTRAINT `FK8C9C2ADC8ADC7080` FOREIGN KEY (`primaryContact_id`) REFERENCES `Contact` (`id`),
  CONSTRAINT `FK8C9C2ADC3FBD3FD6` FOREIGN KEY (`agency_id`) REFERENCES `Agency` (`id`),
  CONSTRAINT `FK8C9C2ADCFF56D2BF` FOREIGN KEY (`location_id`) REFERENCES `Location` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=491 DEFAULT CHARSET=latin1 |
+--------

I also investigated the table indexes:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110503 12:32:12 Transaction:
TRANSACTION 17A89, ACTIVE 0 sec, OS thread id 4493770752 inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 17, query id 31610 localhost root update
insert into Buyer values (5594, CURDATE(), NULL, 4830)
Foreign key constraint fails for table `oppminer`.`buyer`:

  CONSTRAINT `FK3D911938ADC7080` FOREIGN KEY (`primaryContact_id`) REFERENCES `Contact` (`id`)
Trying to add to index `FK3D911938ADC7080` tuple:
DATA TUPLE: 2 fields;
 0: len 8; hex 80000000000012de; asc         ;;
 1: len 4; hex 800015da; asc     ;;

But the parent table `oppminer`.`Contact`
or its .ibd file does not currently exist!

I have two machines that I restored the same dump onto. On the working machine, I can successfully do the same command that causes an error on the problematic machine. Any ideas, stack overflow?

The dump says you were trying to insert `NULL` at the last foreign key failure. — Explosion Pills, May 04 '11 at 04:16
I actually thought I deleted this post, but for anyone that might stumble upon this post, the problem was that MySQL 5.5.11 GA for Snow Leopard had some major issues. Downgrading to 5.5.8 fixed these problems. — hello moto, May 11 '11 at 22:28

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

It has also been fixed in the recently released (May 31) MySQL 5.5.13:
MySQL 5.5 foreign key constraint fails when foreign key exists

edited May 23 '17 at 12:12

Community

1
1

answered Jun 07 '11 at 07:44

penfold

1,523
1
14
21

MySQL Foreign Key Error Even With Existent References

1 Answers1