MySQL – PRIMARY and INDEX keys should not both be set for column …


I set up a table in MySQL today via PHPMyAdmin, and when I set an index on the primary key field this warning came up:

PRIMARY and INDEX keys should not both be set for column ...

I searched around for what this was and why it was bad because well, I don’t want bad things to happen to me. The answer as it turns out is that MySQL automatically creates an index on whatever your primary key is.

So at best, what I did was redundant and at worst it could have caused bad things. I’m not sure though because that was the extent of my research.

I found the answer here, and here.

[tags]MySQL, PHPMyAdmin, Index, Primary Key, Warning, Errors[/tags]

Posted by Greg Pinero (Primary Searcher) on Mar 18th, 2006 and is filed under Other.

Both comments and pings are currently closed.

Add this post to Del.icio.us - Digg

10 Responses to “MySQL – PRIMARY and INDEX keys should not both be set for column …”

  1. Mish says:
    April 14th, 2006 at 5:34 am

    Thanks Greg, was looking for the same answers :)

  2. glouton says:
    May 14th, 2006 at 10:59 am

    Hi every body,

    First of all i would like to ask you to forgive mu english cause i’m just a poor lonesome french but there’s not a long long way from home because i’m in it right now. :-)
    I came here because I was looking for the same answers and today I found the explanation on the Mysql Doc here
    I hope it can help others.
    See you.

  3. glouton says:
    May 14th, 2006 at 11:00 am

    Oups, sorry for not having closed the tag properly but the link works.

  4. Greg Pinero (Primary Searcher) says:
    May 14th, 2006 at 3:22 pm

    Thanks for the tip, Glouton. It’s very appriciated.

    I fixed your link by the way.

  5. Markus Gnerlich says:
    June 25th, 2006 at 10:43 am

    Thanks! You saved me a lot of time.
    In addition, one of the links said that a PRIMARY KEY is, by design, also a UNIQUE index. I set both, and MySQL complained about it with the same error message.

  6. Greg Pinero (Primary Searcher) says:
    June 25th, 2006 at 7:38 pm

    Markus, glad I could help. Thanks for the tip as well.

  7. Greg says:
    January 10th, 2007 at 7:58 pm

    So if I have a table like

    PRIMARY PRIMARY 216 Edit Drop catid
    parentid
    category_parent_id INDEX 54 Edit Drop parentid
    category_child_id INDEX 216 Edit Drop catid

    Which gives an error of ‘PRIMARY and INDEX keys should not both be set for column `catid’

    Do I then remove the two INDEX entries? i.e. for catid and parentid?

    Thanks

    Greg

  8. Greg Pinero (Primary Searcher) says:
    January 10th, 2007 at 8:38 pm

    Greg, remove the index from only catid and you should be good.

  9. rickholt says:
    March 29th, 2007 at 2:22 pm

    I have a table with multiple primary keys. I create an index for each individual key. I get the

    “MySQL – PRIMARY and INDEX keys should not both be set for column …”

    error for the first primary key. If I remove this index, I do not get the error for the other primary keys. Is this normal? The only explanation I see is that the index created for the primary keys is also used as an index for the first primary key. Does anyone know for sure?

  10. Greg Pinero (Primary Searcher) says:
    March 29th, 2007 at 3:25 pm

    rickholt,

    I’m not sure about that. It does appear that way though. Be sure to let us know if you find out though.