Duplicate entry '0' for key 'PRIMARY' Key is set as AI and not mentioned in the query

Question

I'm getting the error: Duplicate entry '0' for key 'PRIMARY'

the query which is being ran is:

INSERT INTO cars (make, model, Reg, colour, miles, price, dealerID, mpg, mph) VALUES ('cake', 'pie', 'k', 'blue', '100', '10', '9', '40', '80')

The primary key for the table is carIndex and is set as Auto Increment, as-well as not being mentioned in the query I don't understand this error. It keeps trying to place the new entry at the very start of the table instead of just adding it on.

The PHP which generates this query is:

function addcar()
{
    session_start();
    if (isset($_SESSION['user']))
    {
        $db = mysqli_connect('localhost', 'root', '', 'cdb')
          or die('Error connecting');
    $query = "INSERT INTO cars (make, model, Reg, colour, miles, price, dealerID, mpg, mph) 
              VALUES (
                '".$_POST['manufacture']."', 
                '".$_POST['model']."', 
                '".$_POST['reg']."', 
                '".$_POST['colour']."', 
                '".$_POST['mileage']."', 
                '".$_POST['price']."', 
                '".$_SESSION['dealerID']."', 
                '".$_POST['mpg']."', 
                '".$_POST['mph']."'
                      )
             "; 

    $addcarquery = mysqli_query($db, $query)
                   or die("Error in query: '$query'");
}
}

Edit:

Table structure, sure how to do the visual example I've seen before so I'll describe.

It is made up of 8 fields, the 7 seen in the query + the Primary key of carIndex, currently the only relation ship is between dealerID and a table called dealers, with carIndex set as Auto Increment.

Edit2:

So.... I restarted XAMPP... and well yeah all seems to work fine now -.-' Sorry y'all.

@Loïc none of the tables or columns are [reserved](https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html). Using backticks will have no effect. — Mike, Apr 17 '14 at 00:13
@Tom Your query is wide open to SQL injection. You should use prepared statements. — Mike, Apr 17 '14 at 00:14
@Loïc Unfortunately magic_quotes [do not stop SQL injection](http://stackoverflow.com/questions/2735749/successful-sql-injection-despite-php-magic-quotes) and simply offer a false sense of security. This is likely one of the main reasons behind deprecating them. The only way to prevent injection when putting user-supplied data into the query is to use one of the dedicated quoting functions (e.g. `mysqli_real_escape_string()`) or prepared statements. — Mike, Apr 17 '14 at 00:35
@Mike here, as every field entry is enclosed between quotes, magic_quotes stop sql injection. — Loïc, Apr 17 '14 at 02:18
@Loïc You didn't read the link I put above, did you? Please educate yourself and stop spreading around these lies. You can start with http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string — Mike, Apr 17 '14 at 03:05
Oh right, that GBK thing again and again. Ok then, AS LONG AS YOU DON'T USE GBK, using magic_quotes and quoting variables in your statements is perfectly safe. — Loïc, Apr 17 '14 at 08:26

score 0 · Answer 1 · answered Apr 17 '14 at 00:19

0

    $query = "INSERT INTO cars (carIndex, make, model, Reg, colour, miles, price, dealerID, mpg, mph) 
              VALUES (
                '',
                '".$_POST['manufacture']."', 
...

answered Apr 17 '14 at 00:19

Loïc

11,804
1
31
49

then you may have another primary key, or you didn't set carIndex as such, please execute the query `describe myTable` so we can see what's going on. – Loïc Apr 17 '14 at 00:33
I'm using XAMPP and have had terrible experiences with it over the years, I just restarted it and now this all works fine :/ sorry. – Vereonix Apr 17 '14 at 00:45

score 0 · Answer 2 · answered Apr 17 '14 at 00:37

One possibility is that it's not the PRIMARY KEY constraint on the cars table that is throwing the error, if there's a trigger being fired that is performing an INSERT. (We can't tell from the name of the constraint PRIMARY that it's definitely the cars table.)

Also, verify that the AUTO_INCREMENT attribute is defined on the PRIMARY KEY column. The output from SHOW CREATE TABLE cars is a quick way to verify the current table definition.

score 0 · Answer 3 · answered Apr 17 '14 at 00:47

You need to provide us tables structure so we can precisely answer to your question.

Anyway, error You get Duplicate entry '0' for key 'PRIMARY' usually means that for some reason your primary key is NOT defined as AUTO_INCREMENT so when you not explicitly set values for it in query it will always evaluate to 0 and will produce error like those one.

To be precise, you will get such error after you try to insert second record. In empty table this query can pass and will assign 0 for your primary key but on next insert query will fail because you already have value 0 of primary key for first inserted record.

Duplicate entry '0' for key 'PRIMARY' Key is set as AI and not mentioned in the query

3 Answers3