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.

  • sandeep

    thanks a lot buddy

  • http://hibbittsconsulting.com 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….

  • http://onlinephduk.com/ 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

    thanks!!!

  • http://www.rimpe.net/ rimpe

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

  • http://www.3dprog.com Patrick

    Nice brief summary.

  • Shukla Alok1

    Thanks for this information…. nice……..

  • http://www.facebook.com/people/Sandeep-Boda/728577803 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

    thx

  • Shinaskollam

    thanx machaa !!!!

  • Moulalishaik123

    thank

  • No1

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

  • http://www.facebook.com/people/Evaline-Cherotich/100001009442002 Evaline Cherotich

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

  • http://www.facebook.com/people/Evaline-Cherotich/100001009442002 Evaline Cherotich

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

  • Qrrrr

    Thanks

  • Rayalla

    tanx its gud for freshers

  • Feesi1

    thanks bud

  • http://uselyrics.blogspot.com/ Sreeram

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

    Regards,

    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

    helpful

  • http://www.mysql.com 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`