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

[Quoted] Jerry Stuckle wrote:

> J.O. is correct and you are wrong (as usual).

[Quoted] No, J.O. and you are wrong. (*You*, and you full-quoting without proper reading, as much too often.)  

> The problem is the backup file does not have the id column in it,

[Quoted] It would be a very ill-conceived target table that has

  `id` INT(…) PRIMARY KEY DEFAULT '0'

and no AUTO_INCREMENT, but it is a possibility.

> or the id column does not have a unique key.

Very likely the dump file has multiple records with the value '0' or NULL in the field value whose column has a PRIMARY KEY constraint; that is the reason why the OP gets *this* error message (in the Subject).

> Dropping the primary key allows the data to be imported, but does not
> create the unique key

Of course it does not. Therefore the *copying* to *another* table *with the same structure* that automatically fixes that later. Once the data has been fixed, the original target table can be DROPped or RENAMEd, and the table with the correct data be RENAMEd to carry the name of the target table.

> (not this is from a backup file - NOT another table!).

[Quoted] A dump file with name suffix “.sql”, as the OP reports, contains a sequence of *(My)SQL statements* for reconstruction of the original data. In the case of a dump file of a table, it contains INSERT statements for the original data, maybe preceded by a CREATE TABLE statement and if so, wisely preceded only by a CREATE TABLE IF NOT EXIST statement. It is at least one [Quoted] of those INSERT statements that fails here. Removing the offending constraint from the *empty* target table allows those INSERT statements to succeed so that the data can be corrected *in the database* *by means of the DBMS*. In fact, it might only be necessary to add AUTO_INCREMENT to the PRIMARY KEY column here.

Only if (for some weird reason here) the index definitions are contained in the dump file as well (which begs the question how the dumped data could be stored in there in the first place: you would have to disable checks before you add the constraint and then create the dump file), the dump file absolutely needs to be edited before importing it. In all other cases one should refrain from editing the dump file so as not to invalidate it. Invalidation includes modifying the attributes of the file and accidentally modifying the original data (e.g. because of encoding issues or whatever bug the editor-du-jour may have).  

> Additionally, even if it is from another table, your suggestion would
> potentially change every primary key in the table - […]

Notice the “e.g.”; it was an *example*. If you prefer, you can also use MAX(`primary_key_column`) + 1 or whatever value you like for ON DUPLICATE KEY UPDATE, so that the number of modified primary key values as compared to the original is minimized.

In fact, if the primary key is not numeric, you have to use a different approach, for example concatenating a character.

-- 
PointedEars

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