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

On 4/11/2017 1:50 AM, J.O. Aho wrote:
> On 04/10/17 21:43, Thomas 'PointedEars' Lahn wrote:

>> J.O. Aho wrote:

>>>>> The problem is the backup file does not have the id column in it,
>>>>
>>>> It would be a very ill-conceived target table that has
>>>>
>>>>   `id` INT(…) PRIMARY KEY DEFAULT '0'
>>>
>>> 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');
>>
>> No, I have tested it.  Jerry is correct about the possibility.
>>
>> In theory, if the target table has
>>
>> | mysql> SHOW CREATE TABLE `t1`\G
>> | […] CREATE TABLE `t1` (
>> |   `id` int(…) NOT NULL DEFAULT '0',
>> |   `foo` VARCHAR(50),
>> |   […]
>> |   PRIMARY KEY (`id`)
>> | ) […]
>>
>> and the offending statement(s) is/are of the form
>>
>>   INSERT INTO `t1` (`foo`) VALUES ('zero'),('one'),('two');

It's also quite possible (or even probable) that rows have been deleted, leaving gaps in the autoincrement values.

[Quoted] In either case the database will be seriously borked with foreign keys pointing at the wrong data.

>
>

>>> 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.
>>
>> And yet it is a distinct possibility.  For a dump of a single table instead 
>> of the whole database, it might even be intentional.

And you're arguing with a well-known troll, J.O. He'll never admit he is wrong.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================