Mysql autoincrement does not increment

user_13770014 0
Viewed 175

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?

auto-increment default-value null mysql
2 Answers

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

instead of >| CustomerID | int(10) unsigned | NO | PRI | NULL |

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
user_51935360

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

user_64832940
Related