Re: Duplicate entry '0' for key 'PRIMARY'

The Natural Philosopher wrote:

> One very faint possibility is that data is being imported into a table
> that is not clean, even if its data has been deleted it may still have
> an 'autofincrement' value implicit in it.
>
> delete * from my_table

The syntax of the (MySQL) DELETE statement is

  DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM `table`[.*] […] [WHERE where_condition];

Therefore, the *proper* (MySQL) statement to delete all records in table `my_table` is either

  DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM `my_table`;

which only requires the DELETE privilege (and the SELECT privilege for columns that are only read) or

  TRUNCATE [TABLE] `my_table`;

which requires the DROP privilege.

The parts in brackets are optional. (Note that “help TRUNCATE;” in the mysql client will output the help screen for the TRUNCATE() *function* for floating-point and fixed-point values, while “help TRUNCATE TABLE;” will output that for the TRUNCATE [TABLE] *statement*.)  

> should always be followed by an
>
> alter table my_table autoincrement=1;

AISB,   ALTER TABLE `my_table` AUTO_INCREMENT=1;

but that is only required if you use DELETE instead of TRUNCATE (among other things, TRUNCATE resets the AUTO_INCREMENT value to its start value).   

> etc. before importing data into it, if its been used before.

AISB. But one of the problems likely to have caused the reported problem is that the AUTO_INCREMENT attribute is _not_ set on the PRIMARY KEY column. I have already explained how to set it and what needs to be considered when doing so. [Does *anyone* here care to *read* *before* posting?]

[Quoted] While arguably standards-compliant per RFC 5536, the From header field value of your posting still constitutes a disregard of Netiquette.

-- 
PointedEars

Twitter: _at_PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.