user2707209 user2707209 - 1 year ago 103
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download