2

I'm currently in the process of converting one of our main ID columns from an INT to a STRING in a MySQL database. The process is pretty simple,

  1. Remove all foreign keys that reference the column (you can't modify it with foreign keys in place)
  2. Modify the ID column and all columns that reference it in other tables
  3. Add the foreign keys back in

Everything is working except for 1 foreign key in step 3 where I get the following error,

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cp`.`#sql-1_13`, CONSTRAINT `widgets_candidate_id_foreign` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`))

I'm no stranger to this error. It usually means I got a column name wrong or something, but in this case I'm pretty sure I haven't unless I'm going mad. See snipped table exports below,

CREATE TABLE `candidates` (
  `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  ---- SNIP ----
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `widgets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `candidate_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  ---- SNIP ----
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2203 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC

And the MODIFY query is,

ALTER TABLE `widgets` 
    ADD CONSTRAINT widgets_candidate_id_foreign FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`);

The only thing I think it could be is the ROW_FORMAT=DYNAMIC on the widgets table. Could candidate_id be stored off-page, hence causing the issue?

Any ideas?

Thanks.

Stephen Melrose
  • 4,772
  • 5
  • 29
  • 42

1 Answers1

1

You can add a foreign key to a table that already has data in it, but only if the child row exists for each row in the parent table. Based on the error that you are receiving, I would consider that you have a value in your child table that has somehow become deprecated in your parent table.

You can look into this further Here.

You can either use a left join or an IN to see which value exist in the child table that does not exist in the parent. There are other ways to go about solving this issue but how you attack the problem is really what makes sense to you in your particular environment.

Community
  • 1
  • 1
BK435
  • 3,076
  • 3
  • 19
  • 27