PhpMyAdmin removes the primary key for MySQL table

MySQLphpmyadminwamp

My problem is this:
I create a table in WAMP Server's phpMyAdmin,
I always make the first column as

name: id
type: int
index: primary
auto_increment: true

then I add other columns , mostly harmless int's or varchar's. And sometimes timestamp's with DEFAULT = CURRENT_TIMESTAMP. I save the table with no problems.

Then when I'm starting to use this table in my php scripts, I add a value without stating a value for id, since it is auto_increment, so like this:

$pdo->query("INSERT INTO mytable(harmless_int, harmless_varchar) VALUES ('42','marvin');");

and when I go to check my table in phpMyAdmin, I see that the value for id is 0.
Further, I click "Structure", then "Change" for column id, and I see that it does not have auto_increment property anymore. So I DROP the table, create it the exact same way again, then it works.

This happened me a lot, I always have to create the same table twice. So what am I doing wrong?

Best Answer

I think this is down to a table copy that you've done.

I can only think that you might have done something like this in the past:

CREATE TABLE [new_table] SELECT * FROM [old_table]

It seems that although it preserves the column names and data, it does not preserve the structure and keys.

How do I know? - I just found out the hard way.

Related Question