Create a MySQL table with a primary key

Posted February 8, 2004 by Quinn McHenry in MySQL

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.

  • sandeep

    thanks a lot buddy

  • Frank

    Cool, that’s what I was looking for

  • asder43

    it was helpful, thanks

  • Cw

    Simple and nice

  • Atchyut

    thank you

  • Anonymous

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

  • kimberley

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

  • Van sen

    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.

  • sinthiya

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

  • Anonymous


  • rimpe

    Thanks a lot. But I have a question,
    Why use that?

  • Patrick

    Nice brief summary.

  • Shukla Alok1

    Thanks for this information…. nice……..

  • Sandeep Boda

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

    • Khehlamarema

      yes u can have a secondary key

  • Ntb

    thx alots

  • Piotr


  • Shinaskollam

    thanx machaa !!!!

  • Moulalishaik123


  • No1

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

  • Evaline Cherotich

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

  • Evaline Cherotich

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

  • Qrrrr


  • Rayalla

    tanx its gud for freshers

  • Feesi1

    thanks bud

  • Sreeram

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


    Sreeram Veerapaneni

  • Pacopag

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

  • Saaaa

    thx, very helpful for beginners !!

  • sailu

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

  • honey

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

  • C@N

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

  • Naveen

    Thnks dude…………………..

  • Tehyun

    Thank you for the explanation!

  • jithin


  • MySqL Dev

    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`