is this possible to made two primary key in one table

Question

hi i want to know that is is possible to make primarykey in one table in mysql. if yes please tell me the concept behind this. because i have seen a table in which two primary key is there with no auto increment set

Answer 1

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

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

Answer 2

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 3

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)   <====
    )

Answer 4

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 5

A table can have a single PRIMARY key, which may consist of one or more columns. A table can also have a number of additional keys defined on it, as UNIQUE KEY constraints.

It's not clear from your description whether you were looking at a table with multiple keys defined, or a table with a multi-column PRIMARY KEY.

Answer 6

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