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
– Majid Fouladpour, Commented Sep 7, 2020 at 12:15
Does this answer your question? Alter a MySQL column to be AUTO_INCREMENT — Cid
– Cid, Commented Sep 7, 2020 at 12:16

Cid · Accepted Answer · 2020-09-07 12:17:19Z

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 7, 2020 at 12:17

Cid

15.3k4 gold badges33 silver badges49 bronze badges

answered Sep 7, 2020 at 12:15

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

awan · Accepted Answer · 2020-09-07 12:19:12Z

0

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

answered Sep 7, 2020 at 12:19

awan

271 silver badge7 bronze badges

Collectives™ on Stack Overflow

Mysql autoincrement does not increment

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related