Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index.

MySQL accepts this query:

CREATE TABLE IF NOT EXISTS `poll_option_i18ns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_option_id` int(11) NOT NULL,
  `lang` char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `title` varchar(127) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`poll_option_id`,`lang`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `poll_option_i18ns`
  ADD CONSTRAINT `poll_option_id` FOREIGN KEY (`poll_option_id`) REFERENCES `poll_options` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

But in MySQL workbench I couldn't add foreignkey without index! It is MySQL Workbench bug or I do it wrong way?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

That's currently a limitation of MySQL Workbench and has been mentioned recently already on Stackoverflow (can't find the question right now, sorry). MySQL Workbench doesn't use the special case where a foreign key uses columns that start the PK in that table. Please file a feature request at http://bugs.mysql.com if you think it would be worth implementing it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.