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?

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.