Is there no use in defining auto increment column as primary key

In php myadmin it says it is no use in defining auto increment column as primary key for table. you can remove primary key constrain. (since both do the same job like).

Is this true. should I remove primary key constrain?

won't it good t开发者_运维知识库o have a primary key column for where clause rather than auto increment column

Keep The PK constraint. It will save you from some trouble:

• if you intend to use some frameworks which check for this constraint to determine which columns are used as PK. They will be lost or require additional configuration if the constraint is not present.
• When you'll use DB design software it will work better if your DB is properly designed.
• When you'll have to change your DB software (upgrade or change brands) You will be happy to have all the constraints properly defined in your SQL statements.

You can create a column that is auto_incrementing without it being the primary key:

This statement is legal:

mysql> create table silly_but_possible (
    id int auto_increment not null,
    xx varchar(9),
    key(id),
    primary key (xx));
Query OK, 0 rows affected (0.15 sec)

So if you want your auto_incrementing column to be the PK, you'd better define it as such.
Note that a table (at present) can only have one auto_incrementing column.

What happens if you don't define a PK?
If you don't MySQL will define a hidden auto_incrementing PK for you.
However if you already have a auto_increment column, that column will always have an index, and because there can only be one auto_incrementing column, MySQL (at present!) has no other choice than to promote that row to the primary key.

when u define a column as a auto_increment it must be key(primary , unique ,index).

take an example

mysql> create table test

-> (id int(5) auto_increment,
-> name char(10)
-> );

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

so when we define a column as auto_increment it should be key.So it is better to define the column as PK.

The primary index is used by the database manager for efficient access to table rows, and allows the database manager to enforce the uniqueness of the primary key.