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.