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

[Quoted] On 04/10/17 06:39, Thomas 'PointedEars' Lahn wrote:
> Jerry Stuckle wrote:
>

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

[Quoted] Sure you can try to do it in the database, but can cause the result to be missed constraints when fixing everything. But sure, you can mess up the dump file by editing it.

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

[Quoted] This would also require that original table didn't have the id (in your example) column.

as the data dump would otherwise have the primary key.

INSERT INTO `t1` VALUES (0,'zero'),(1,'one'),(2,'two');

> 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.

[Quoted] Apparently it don't, as the table wouldn't have the either the constraint or not two rows with primary key 0.

  It is at least one
> 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.

[Quoted] Only if the original table didn't have the primary key column, for a [Quoted] table with a primary key column would have the following dump data:

INSERT INTO `t2` VALUES (1,'zero'),(2,'one'),(3,'two');

[Quoted] Just think of the idea where you wouldn't have the primary key and you insert data into another database, which may been used and tables just truncated, so then the auto_increment wouldn't necessarily start from 1 and you would suddenly have an inconsistent database and a lot of errors when key constraints wouldn't work.

-- 

 //Aho