The Ultimate Guide to High Availability with MariaDB

possible inconsistency on foreign key definition

You are viewing an old version of this question. View the current version here.

hello - i am trying to wrap my head around foreign keys in 10.6. the following behavior does not make sense to me. This code block works just fine:

CREATE TABLE `myParent` (
`myKeyValue` varchar(10) NOT NULL,
KEY  (`myKeyValue`)
) ENGINE=INNODB;

CREATE TABLE `myChild` (
`myKeyValue` varchar(10) NOT NULL,
                      /*          KEY `myParent_to_myChild` (`myKeyValue`),   */
FOREIGN KEY `myParent_to_myChild` (`myKeyValue`) REFERENCES `myParent`(`myKeyValue`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB;

please notice i did not specify a key for the child.

however, when i create a child key and attempt to drop the child key, apparently i am not allowed to do so:

CREATE TABLE `myParent` (
 `myKeyValue` varchar(10) NOT NULL,
 KEY   (`myKeyValue`)
) ENGINE=INNODB;

CREATE TABLE `myChild` (
`myKeyValue` varchar(10) NOT NULL,
                        KEY `myParent_to_myChild` (`myKeyValue`),
FOREIGN KEY `myParent_to_myChild` (`myKeyValue`) REFERENCES `myParent`(`myKeyValue`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB;

ALTER TABLE `myChild` DROP KEY `myParent_to_myChild` ;

ERROR 1553 (HY000) at line 20: Cannot drop index 'myParent_to_myChild': needed in a foreign key constraint

why is it in the first example, no key is required, but in the second example, it is required?

Answer Answered by Mark Edwards in this comment.

ok it appears that if the key is not specified, MariaDB creates it for us, using the same name as the constraint.

apologies for the studid question/post.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.