user2707209 user2707209 - 8 months ago 30
MySQL Question

Alter table not adding foreign key MYSQL

When I execute the below, it looks like it sets up a regular KEY, not a foreign key:

ALTER TABLE `dev1_IMGStoreTmpComponents`
ADD CONSTRAINT `con_fk_templates`
FOREIGN KEY `fk_templates`(`templateId`)
REFERENCES `dev1_IMGStoreTemplates`(`templateId`)


SQL runs successfully.

then I run this:

SHOW CREATE TABLE `dev1_IMGStoreTmpComponents`;

CREATE TABLE `dev1_IMGStoreTmpComponents` (
`componentId` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`description` varchar(250) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`templateId` int(10) unsigned NOT NULL,
`type` char(3) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`layer` tinyint(3) unsigned DEFAULT NULL,
`posx` mediumint(8) unsigned NOT NULL,
`posy` mediumint(8) unsigned NOT NULL,
`width` mediumint(8) unsigned NOT NULL,
`height` mediumint(8) unsigned NOT NULL,
`settings` text COLLATE utf8mb4_unicode_520_ci,
PRIMARY KEY (`componentId`),
UNIQUE KEY `componentId` (`componentId`,`templateId`),
KEY `con_fk_templates` (`templateId`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci COMMENT='Template Components'


Why don't I see the CONSTRAINT in the Create table?

Answer Source

MyISAM storage engine doesn't support foreign key constraints.

ENGINE=MYISAM 

InnoDB storage engine supports referential integrity constraints. (Both the referencing table and referenced table need to be defined with InnoDB storage engine.)

ENGINE=INNODB

MySQL does accept the foreign key constraint syntax for a MyISAM table, but MySQL disregards the foreign key. The most likely reason that the syntax is accepted is to make migration of DDL from other databases easier.