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.