Is it possible to make two primary keys in one table?

Question

Hi I want to know if it is possible to make two primary keys in one table in MySQL. If so, please explain the concept behind this. I am asking because I have seen a table in which two primary keys are there with no auto increment set.

Answer 1

you can only have 1 primary key, but:

• you can combine more than one column to be the primary key (maybe it's this what you have seen)
• the primary key don't needs to be an auto-increment, it just has to be unique
• you can add more than one index to one or more colums to speed up SELECT-statements (but slow down INSERT / UPDATE)
• those indexes can be marked as unique, wich means they don't let you insert a second row with the same content in the index-fields (just like a primary key)

Answer 2

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

[...] A table can have only one PRIMARY KEY. [...]

Answer 3

No, but you can have other UNIQUE indexes on the table, in addition to the PRIMARY KEY. UNIQUE + NOT NULL is basically the same as a primary key.

What you have seen is probably a composite primary key (more than one column making up the unique key).

Answer 4

Use a Composite Primary Key...

e.g.

CREATE TABLE table1 ( 
   first_id int unsigned not null, 
   second_id int unsigned not null auto_increment, 
   user_id int unsigned not null, 
   desc text not null, 
PRIMARY KEY(first_id, second_id));

Also, check out the example here

Answer 5

You can use multiple columns for your primary key in this way:

CREATE TABLE
    newTable
    ( field1 INT(11)
    , field2 INT(11)
    , field3 VARCHAR(5)
    , field4 BLOB
    , PRIMARY KEY (field2, field1, field3)   <====
    )