MySQL innodb foreign keys

Question

Why will this sql not create the foreign key?

DROP TABLE IF EXISTS `flow_people`;
CREATE TABLE `flow_people` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `active` enum('0','1') COLLATE utf8_unicode_ci DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `token` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `unixtime` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `company_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `salutation` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `middle_name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `dob` date NOT NULL,
  `cams` decimal(7,0) NOT NULL,
  `location_id` int(8) NOT NULL,
  `people_type` int(8) NOT NULL COMMENT 'flow_people_types',
  `comment` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`people_type`) REFERENCES flow_people_types(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1666 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

the table flow_people_types exists, it has an ID column.

I think I am missing something fundamental here, what is the problem?

Answer 1

Check this link MySQL Foreign Keys:

Try this:

DROP TABLE IF EXISTS `flow_people`;
CREATE TABLE `flow_people` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `active` ENUM('0','1') COLLATE utf8_unicode_ci DEFAULT '0',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `token` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `unixtime` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `company_name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `salutation` VARCHAR(4) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_name` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL,
  `middle_name` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL,
  `dob` DATE NOT NULL,
  `cams` DECIMAL(7,0) NOT NULL,
  `location_id` INT(8) NOT NULL,
  `people_type` INT(8) NOT NULL COMMENT 'flow_people_types',
  `comment` LONGTEXT COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `FK_flow_people_FPT` (`people_type`),
  CONSTRAINT `FK_flow_people_FPT` FOREIGN KEY (`people_type`) REFERENCES flow_people_types(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=1666 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;