MySQL 帮助无法删除行(errno : 150) foreign key constraint

Question

Uppon 试图删除我的一个表中的记录，我得到以下信息。

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`pasremotedb`.`plans`, CONSTRAINT `FK_plans` FOREIGN KEY (`plan_id`) REFERENCES `plan_options` (`plan_id`))

所以我做了一些研究，看起来我有一些乱七八糟的外键。我试图删除 key ，但后来我得到了这个。

mysql> ALTER TABLE `plan_options` DROP INDEX `plan_id`;
ERROR 1025 (HY000): Error on rename of './pasremotedb/#sql-1c0f_31ea' to './pasremotedb/plan_options' (errno: 150)

做了一些更多研究并决定做一个SHOW INNODB STATUS来得到LATEST FOREIGN KEY ERROR结果是:

110824 15:07:33 Error in foreign key constraint of table pasremotedb/plans:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT "FK_plans" FOREIGN KEY ("plan_id") REFERENCES "plan_options" ("plan_id")
The index in the foreign key in table is "PRIMARY"
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

---

这就是我所能得到的。我对 MySQL dbs 还不是很好，而且在涉及外键时肯定会挂断电话。有人可以帮忙吗？

看起来涉及的两个表是plans和plan_options。两者的结构如下:

--
-- Table structure for table `plan_options`
--

CREATE TABLE IF NOT EXISTS `plan_options` (
  `account_id` int(11) NOT NULL,
  `plan_id` tinyint(1) NOT NULL,
  `discipline_id` int(2) NOT NULL,
  `practice_type_id` int(1) NOT NULL,
  `discipline_other` varchar(100) NOT NULL,
  PRIMARY KEY (`account_id`),
  KEY `plan_id` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `plan_options`
--

INSERT INTO `plan_options` (`account_id`, `plan_id`, `discipline_id`, `practice_type_id`, `discipline_other`) VALUES
(1, 3, 5, 1, ''),
(2, 2, 3, 1, ''),
(3, 1, 6, 1, ''),
(4, 2, 1, 2, ''),
(5, 3, 1, 1, ''),
(6, 2, 5, 1, ''),
(7, 2, 3, 1, ''),
(12, 2, 7, 2, 'MD'),
(13, 1, 2, 2, ''),
(14, 3, 1, 2, ''),
(16, 1, 1, 2, ''),
(18, 2, 7, 1, 'AMA Guides'),
(21, 2, 5, 1, '');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `plan_options`
--
ALTER TABLE `plan_options`
  ADD CONSTRAINT `FK_plan_options` FOREIGN KEY (`account_id`) REFERENCES `account_details` (`account_id`);
--
-- Table structure for table `plans`
--

CREATE TABLE IF NOT EXISTS `plans` (
  `plan_id` tinyint(1) NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(50) NOT NULL,
  PRIMARY KEY (`plan_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `plans`
--

INSERT INTO `plans` (`plan_id`, `plan_name`) VALUES
(1, 'Gold'),
(2, 'Platinum'),
(3, 'Supremacy');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `plans`
--
ALTER TABLE `plans`
  ADD CONSTRAINT `FK_plans` FOREIGN KEY (`plan_id`) REFERENCES `plan_options` (`plan_id`);

Answer 1

外键在plan表上，so you would want to drop it form there :

ALTER TABLE `plans` DROP FOREIGN KEY `FK_plans`

但请注意，这把 key (以及它正在阻止您的事实)可能是您想要的东西。它存在的原因是为了防止选项进入与计划无关的 plan_options 表。因此，您可能希望在删除时从两个表中删除:

DELETE FROM plans, plan_options
  WHERE plan_id = ?