Mysql autoincrement does not increment

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?

Solution

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