What is a KEY (not PK or FK) used for on MySQL?

Asked Apr 19 '18 at 16:33

Active Apr 19 '18 at 16:33

Viewed 34 times

I have the next SQL query:

 DROP TABLE IF EXISTS `Genre_Movie`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Genre_Movie` (
      `id_genre` int(11) NOT NULL DEFAULT '0',
      `id_movie` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id_movie`,`id_genre`),
      KEY `id_genre` (`id_genre`),
      CONSTRAINT `Genre_Movie_ibfk_1` FOREIGN KEY (`id_movie`) REFERENCES `Movie` (`id_movie`),
      CONSTRAINT `Genre_Movie_ibfk_2` FOREIGN KEY (`id_genre`) REFERENCES `Genre` (`id_genre`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

But I don't really know what does the following line does:

KEY `id_genre` (`id_genre`),

Can someone explain me what is the use of this line, creating a key that is not Foreign or Primary? Thanks.

asked Apr 19 '18 at 16:33

theMaximumForce

1

It creates an index that makes searching that column faster. Foreign keys are required to be indexed (if you don't create the index explicitly, it will be created automatically when you declare the FK). – Barmar Apr 19 '18 at 16:37
thanks, so, if I delete that line, the key would be automatically generated when doing the foreign key id_genre two lines after, nope? @Barmar – theMaximumForce Apr 19 '18 at 16:41
1

Yes, that's correct. – Barmar Apr 19 '18 at 16:43
thanks. If you create the answer not like a comment I will give you the point. thanks again. @Barmar – theMaximumForce Apr 19 '18 at 16:44
Can't answer closed questions. – Barmar Apr 19 '18 at 16:45
oh, sorry, it's closed because it is duplicated. :/ – theMaximumForce Apr 19 '18 at 16:46
I know, I closed it. – Barmar Apr 19 '18 at 16:48

What is a KEY (not PK or FK) used for on MySQL?

0 Answers0