Difference Between Primary Key And Unique Key And Foreign Key And Composite Key

Primary Key

1. Primary Key can identify a row as uniquely 
2. A table can have  only one primary key
3. It can't be Null
4. Indexing added automatically to Primary key

Foreign Key

1. A FOREIGN KEY in one table reference to a Primary Key in another table
2. A table can have  one OR more foreign key.
3. It can be Null 
4. Indexing not added automatically to Foreign key

Unique Key

1. Unique Key can identify a row as uniquely.
2. A table can have  one OR more unique key.
3. It can be Null
4. Indexing not added automatically to Unique Key

Composite Key

1. A composite key contains at least one compound key and one more attribute. Composite keys may also include simple keys and non-key attributes.
2. A table can have one OR more composite Key
3. It can also be null.

See Example:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `user_profile` (
  `user_id` int(11) NOT NULL,
  `address1` varchar(100) NOT NULL,
  `address2` varchar(100) NOT NULL,
  KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Primary Key: users.id
Foreign Key: user_profile.user_id
Unique Key:  users.email