Mysql autoincrement does not increment

Question

I have this table:

mysql> desc Customers;

+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| CustomerID | int(10) unsigned | NO   | PRI | NULL    |       |
| Name       | char(50)         | NO   |     | NULL    |       |
| Address    | char(100)        | NO   |     | NULL    |       |
| City       | char(30)         | NO   |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+

Now, If I want to insert sample data:

mysql> insert into Customers values(null, 'Julia Smith', '25 Oak Street', 'Airport West');
ERROR 1048 (23000): Column 'CustomerID' cannot be null

I know I cannot make the ID null, but that should be job of mysql to set it numbers and increment them. So I try to simple not specifying the id:

mysql> insert into Customers (Name, Address, City) values('Julia Smith', '25 Oak Street', 'Airport West');
Field 'CustomerID' doesn't have a default value

Now I am in trap. I cannot make id null (which is saying for mysql "increment my ID"), and I cannot omit it, becuase there is no default value. So how should I make mysql to handle ids for me in new insertions?

What does your `CREATE TABLE` query look like? *It is the job of MySQL to increment the number* only for a field that is set to be `auto_increment`. If your `CustomerID` was `auto_increment` you'd see it in the `Extra` column. — Majid Fouladpour, Sep 07 '20 at 12:15
Does this answer your question? [Alter a MySQL column to be AUTO\_INCREMENT](https://stackoverflow.com/questions/2169080/alter-a-mysql-column-to-be-auto-increment) — Cid, Sep 07 '20 at 12:16

score 1 · Accepted Answer · edited Sep 07 '20 at 12:17

1

Primary key means that every CustomerID has to be unique. and you defined it also as NOT NULL, so that an INSERT of NULL is not permitted

Make it

CustomerID  BIGINT AUTO_INCREMENT PRIMARY KEY

and you can enter your data without problem

ALTER TABLE table_name MODIFY CustomerID BIGINT  AUTO_INCREMENT PRIMARY KEY

edited Sep 07 '20 at 12:17

Cid

14,968
4
30
45

answered Sep 07 '20 at 12:15

nbk

45,398
8
30
47

score 0 · Answer 2 · answered Sep 07 '20 at 12:19

@Milan,

Delete the CustomerID var from the table. And add this field again with the following details:

Field: CustomerID, Type: BIGINT(10), Default: None, Auto_increment: tick in the checkbox

Click SAVE button to save this new field in the table. Now I hopefully it will work while inserting the new record. Thanks.

Mysql autoincrement does not increment

2 Answers2