Unique key can use as primary key then why we keep another primary key?

Question

CREATE TABLE IF NOT EXISTS `tbl_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE latin1_general_ci NOT NULL,
  `passd` varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

We have usermane as unique, we can use it as primary key, So what is the advantage of keeping id as primary key?

Thanks

Answer 1

Primary key will avoid duplicate and null values in a table which the combination of unique+not null will do.But the advantage is,if a table has primary key we can create relation to child tables.

This Will Help You

Edit: Primary Key

1. Primary Key can't accept null values.
2. By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
3. We can have only one Primary key in a table.
4. Primary key can be made foreign key into another table.

Unique Key

1. Unique key can accept only one null value.
2. By default, Unique key is a unique non-clustered index.
3. We can have more than one unique key in a table.
4. In SQL Server, Unique key can be made foreign key into another table.

Answer 2

Primary Key:

i) Can be only one in a table

ii) It never allows null values

iii) Primary Key is unique key identifier and can not be null and must be unique.

Unique Key:

i) Can be more than one unique key in one table.

ii) Unique key can have null values

iii) It can’t be candidate key

iv) Unique key can be null and may not be unique.

CREATE TABLE IF NOT EXISTS `tbl_users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL,
  `passd` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;