In MySQL, how to increment a Primary Key in a INSERT statement

Question

I want to manually add another row in my table CUSTOMER while incrementing the CustomerID attributes.

This is how my CUSTOMER table is structured :

 ------------------------------------------------------
 | CustomerID          |   FirstName  |    LastName   |
 ------------------------------------------------------
 | INTEGER PRIMARY KEY |   CHAR(15)   |    CHAR(15)   |
 ------------------------------------------------------

In this table, I already have a row with CustomerID = 1, FirstName = Jeremhia, LastName = Cutecut. I want to add another row in this table without specifying the number in column CustomerID, in other words, I want to increment it.

This is what I did so far and it is not working :

INSERT INTO CUSTOMER (CustomerID, FirstName, LastName)
      VALUES (:vcustid, 'Abe', 'Lincoln');

I suspect that the variable vcustid is not working in phpMyAdmin, is there a similar variable or any other way to increment it so I got this following results in my table?

------------------------------------------------------
| CustomerID          |   FirstName  |    LastName   |
------------------------------------------------------
|         1           |   Jeremhia   |    Cutecut    |
------------------------------------------------------
|         2           |   Abe        |    Lincoln    |
------------------------------------------------------

If the CurtomerID field was created as a primary key with `auto_increment`, then you only need to insert the first and last names. — Paul T., Jul 12 '17 at 21:15
Do try and use `VARCHAR(255)` as a generic "string" column type. It's usually way too restrictive to clamp this down to a measly 15 characters. — tadman, Jul 12 '17 at 21:18

Juan Carlos Oropeza · Accepted Answer · 2017-07-12T21:17:36.600

4

Create your table with AUTO_INCREMENT

CREATE TABLE CUSTOMER (
     CustomerID  INT NOT NULL AUTO_INCREMENT,
     FirstName  CHAR(30) NOT NULL,
     LastName  CHAR(30) NOT NULL
     PRIMARY KEY (CustomerID)
);

And let the db handle the PK

INSERT INTO CUSTOMER (FirstName, LastName)
       VALUES ('Abe', 'Lincoln');

edited Jul 12 '17 at 21:17

answered Jul 12 '17 at 21:16

Juan Carlos Oropeza

47,252
12
78
118

If this is the case then you omit `CustomerID` on insert and it's populated for you automatically. – tadman Jul 12 '17 at 21:17
That's it! Instead of specifying the primary key at the end, at the second row I did this : CustomerID INTEGER PRIMARY KEY AUTO_INCREMENT, and it worked like a charm. Thank you for pointing me out the auto_increment attribute! – Jul 13 '17 at 06:13

score 0 · Answer 2 · answered Jul 12 '17 at 21:21

0

You can also add the property AUTO_INCREMENT to the field to let sql handle the issue. you would then only pass the values

ALTER TABLE CUSTOMER MODIFY COLUMN CustomerID INT auto_increment

INSERT INTO CUSTOMER (CustomerID, FirstName, LastName)
      VALUES ('Abe', 'Lincoln');

answered Jul 12 '17 at 21:21

Chris

110
5

What happen if `CustomerID` already have data, what is the next `ID`? – Juan Carlos Oropeza Jul 12 '17 at 21:22
you can set the start point with `AUTO_INCREMENT=x` `x` being the new start point – Chris Jul 12 '17 at 21:25
I see, then you have to be care full with the `MODIFY COLUMN`. – Juan Carlos Oropeza Jul 12 '17 at 21:27
yeah you would only use it if you needed to keep the data, your way is the correct way if you are in development still. – Chris Jul 12 '17 at 21:28

In MySQL, how to increment a Primary Key in a INSERT statement

2 Answers2