[Error] Invalid row key specified: ~ , referred to in ~

Tadashi Shigeoka ·  Sun, November 13, 2011

In Symfony1.4, I got an error when trying to insert data from a fixture file into the database.

# php symfony doctrine:data-load

■ Error Content

Invalid row key specified: (user) , referred to in (rental) Rental_4

Due to MySQL’s foreign key constraints, I hadn’t entered values in the related sections, causing the error.

mysql> show create table rental;
+--------+--| Table  | Create Table
---------------------------------------+
| rental | CREATE TABLE `rental` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `rented_at` datetime DEFAULT NULL,
  `returned_at` datetime DEFAULT NULL,
  `delete_flag` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rental_sluggable_idx` (`slug`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `rental_user_id_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------+----------------------------+

Because of this configuration, I couldn’t set columns with foreign keys to NULL.

CONSTRAINT `rental_user_id_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE

■ Solution

Log into MySQL and delete the foreign key with the following command.

・参考:MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 9.4.4 FOREIGN KEY 制約

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Also, regarding how to write fixture files for columns with relationships, you should not write them as follows:

Rental:
  Rental_1:
    User:               [User_1]
    rented_at:          '2011-01-01'
    returned_at:
    delete_flag:        0
  Rental_2:
    User:
    rented_at:
    returned_at:
    delete_flag:        0

Writing User: like in Rental_2 will cause it to look for a corresponding User object, and if it doesn’t exist, an error occurs.

Instead, you can write user_id: instead of User:, or you can omit user_id: entirely if you’re not entering a value.

That’s all from the Gemba.