Duplicate entry '0' for key 1

[Anon]

I get the above error message when trying to add a field called ID. I use INT, Auto_increment and Primary.

What am I doing wrong?

Cheers.

[[deleted]]

You are trying to add a field?
Do you mean adding a column to an existing table?

[Anon]

Yes

[[deleted]]

Looks like you can't do that with an auto_increment and primary key at the same time.

Adding an INT column will will it with zero's, declaring it primary-key will fail because you have more than one zero in it.

Try dumping the table to text using mysqldump, then drop the table, re-create the table with the id colum (always use lowercase letters for tablenames) and import the data from the dump.

[Anon]

Thanks for your reply. I have tried this using:

CREATE TABLE family (id TINYINT not null , name TEXT not null , forename TEXT not null , origin TEXT not null , company TEXT not null , address TEXT not null , city TEXT not null , date TEXT not null , source TEXT not null , PRIMARY KEY (id))

it works fine.

Then I insert the data. When I browse on PHPMyAdmin it shows only 1 record.

If I drop the id column it then shows 54 records, there are 106 on the file I loaded to it.

What's going on?

[[deleted]]

Does the data you insert include the id field already?

[Anon]

No.

[[deleted]]

id TINYINT not null

one thing to change is that: never use TINYINT for auto_increment fields, always use INTEGER. TINYINT is waaaay too small.

[Anon]

Aaah, that sorted it. Thanks for your help.

[[deleted]]

You're welcome. (you had me wondering for a while🙂 )