Create a MySQL table with a primary key

A primary key uniquely identify a row in a table. One or more columns may be identified as the primary key. The values in a single column used as the primary key must be unique (like a person’s social security number). When more than one column is used, the combination of column values must be unique.


When creating the contacts table described in Create a basic MySQL table, the column contact_id can be made a primary key using PRIMARY KEY(contact_id) as with the following SQL command:

CREATE TABLE `test1` (
contact_id INT(10),
name VARCHAR(40),
birthdate DATE,
PRIMARY KEY (contact_id)
);

Additional columns can be identified as part of the primary key with a comma separated list in the PRIMARY KEY command, like PRIMARY KEY (contact_id, name).

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

35 Responses to “Create a MySQL table with a primary key”

  1. January 27, 2009 at 7:00 am, sandeep said:

    thanks a lot buddy

    Reply

  2. February 04, 2009 at 5:58 pm, Frank said:

    Cool, that’s what I was looking for

    Reply

  3. February 06, 2009 at 11:29 pm, asder43 said:

    it was helpful, thanks

    Reply

  4. June 23, 2009 at 6:28 am, Cw said:

    Simple and nice

    Reply

  5. November 14, 2009 at 1:26 pm, Atchyut said:

    thank you

    Reply

  6. January 07, 2010 at 5:08 pm, Anonymous said:

    thanks for the comments…..
    very useful for my study……..

    Reply

  7. January 21, 2010 at 12:04 am, kimberley said:

    thank you, its really usable to us students…
    it helps us to learn….

    Reply

  8. February 02, 2010 at 8:29 pm, Van sen said:

    Thanks !
    Can you define a primary key once the table is created ?
    My table is already created and I would like to set the primary key at this point.

    Reply

  9. February 04, 2010 at 10:23 am, sinthiya said:

    thank you!!! but if i want to add primary key after entering the data inside the table what i do for it?

    Reply

  10. February 16, 2010 at 10:28 pm, Anonymous said:

    thanks!!!

    Reply

  11. February 26, 2010 at 10:05 pm, rimpe said:

    Thanks a lot. But I have a question,
    KEY `TITLE_INDEX` (`TITLE`),
    Why use that?

    Reply

  12. April 18, 2010 at 1:34 pm, Patrick said:

    Nice brief summary.

    Reply

  13. August 11, 2010 at 8:09 am, Shukla Alok1 said:

    Thanks for this information…. nice……..

    Reply

  14. September 02, 2010 at 1:35 pm, Sandeep Boda said:

    can i have secondary key ?
    can i load data by verifying both keys ?

    Reply

    • November 09, 2010 at 12:47 pm, Khehlamarema said:

      yes u can have a secondary key

      Reply

  15. September 07, 2010 at 3:31 am, Ntb said:

    thx alots

    Reply

  16. October 20, 2010 at 7:05 am, Piotr said:

    thx

    Reply

  17. December 15, 2010 at 4:37 am, Shinaskollam said:

    thanx machaa !!!!

    Reply

  18. December 16, 2010 at 5:27 am, Moulalishaik123 said:

    thank

    Reply

  19. January 05, 2011 at 10:19 pm, No1 said:

    Anyone got the MySql command for deleting a row based on its primary key?

    Reply

  20. January 28, 2011 at 6:55 am, Evaline Cherotich said:

    this is a good summary which has helped me a lot in my study.thanx

    Reply

  21. January 28, 2011 at 6:56 am, Evaline Cherotich said:

    this was of great use to my study.thanx a lot

    Reply

  22. February 08, 2011 at 6:38 am, Qrrrr said:

    Thanks

    Reply

  23. April 11, 2011 at 9:12 am, Rayalla said:

    tanx its gud for freshers

    Reply

  24. June 21, 2011 at 3:12 pm, Feesi1 said:

    thanks bud

    Reply

  25. December 05, 2011 at 3:16 am, Sreeram said:

    Thanks a lot …..Is there any option in the mysql table structure it selft rather than creating the table……

    Regards,

    Sreeram Veerapaneni

    Reply

  26. December 09, 2011 at 11:18 am, Pacopag said:

    Tech recipes rocks! Just had to say thanks for the countless times this site has helped me out.

    Reply

  27. January 11, 2012 at 12:53 am, Saaaa said:

    thx, very helpful for beginners !!

    Reply

  28. March 30, 2012 at 9:21 am, sailu said:

    Its good.but can u show how to create a primary key when the column name is declared..Then it would be very helpful

    Reply

  29. April 12, 2012 at 4:49 am, honey said:

    ne bonda….idi chinna command maku telusu over act cheyku…k

    Reply

  30. May 12, 2012 at 6:26 pm, C@N said:

    Thanks a lot Quinn. I’ve been trying to find this for hours. :D :D

    Reply

  31. May 29, 2012 at 6:50 am, Naveen said:

    Thnks dude…………………..

    Reply

  32. August 10, 2012 at 2:53 am, Tehyun said:

    Thank you for the explanation!

    Reply

  33. February 16, 2013 at 4:12 am, jithin said:

    helpful

    Reply

  34. June 08, 2013 at 4:12 pm, MySqL Dev said:

    Kids, thats wrong, you should always pass auto_increment argument for your pirmary key column id, to increase if no increasing or changing order specified.


    CREATE TABLE `test1` ( contact_id INT(10) auto_increment,
    name VARCHAR(40),
    birthdate DATE,
    PRIMARY KEY (contact_id)
    );

    // Notice `contact_id int(10) auto_increment`

    Reply

Leave a Reply