This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

possible inconsistency on foreign key definition

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.

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.