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

Best Related Posts are Following:

1. Difference between MVC and MVP design pattern.
2. Difference between inner join and outer join in MySQL.
3. Difference between theme and template in website.
4. Difference between in_array and array_key_exists in php with Example.
5. Difference between SSL and HTTPS.
6. Difference between overloading and overriding in php with Example.
7. What is difference between natcasesort and sort?.
8. Difference Between Hadoop and Big-Data.
9. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
10. PHP Sessions and Cookie and Security.
11. Difference between WebService and API.
12. Difference Between MyISAM and innoDB - MySQL.