Superior University Depalpur

 




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

Post a Comment

 
Top