I am trying to understand the use of composite primary key or two primary keys in a table Today someone asked me about composite primary key two make two column as primary key but i unable to understand the logic behind it because we have unique index option to make a column unique, and if I think about foreign key relationship then again its not required.

If anyone have good explanation with example then please clarify that.

CREATE TABLE `sample` (
  `pri_1` int(11) NOT NULL AUTO_INCREMENT,
  `pri_2` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `value` varchar(25) NOT NULL,
  PRIMARY KEY (`pri_1`,`pri_2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
It comes in handy especially when combining with foreign keys ... 

imagine having 3 tables 

T1
ID, data1, data2
T2
ID, data1, data2
T3
T1id, T2id, data3, data4


the ID column for T1 and T2 is obviously primary key - but what about table T3 - we could define another ID column to make a primary key .. OR just define the combo of T1id and T2id as the primary key ... but this depends on what data you hold in your tables and what relationship they contain
but especially when T3 was used to store a M:N (many : many) relation it makes sense to use a composite primary key consisting of T1id and T2id.