am on a MySQL database and am using MySQL workbench.
here’s my table DDL
CREATE TABLE `contract_validator` ( `id` bigint(20) NOT NULL, `created` datetime(6) NOT NULL, `user_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1
I want to make an id column in a table auto_increment, i did so using
SET FOREIGN_KEY_CHECKS = 0; ALTER TABLE validator MODIFY id bigint AUTO_INCREMENT; SET FOREIGN_KEY_CHECKS = 1;
I have two issues, i want to set the auto_increment to max(id) + 1
i have tried:
SELECT @validator_id := max(id) + 1 FROM validator; ALTER TABLE validator AUTO_INCREMENT = @validator_id ;
but i get syntax error,
the second issue is that when i try to insert a line i get an error
Error Code: 1364. Field 'id' doesn't have a default value
I don’t know what I did wrong,
thank you for your help.
Normally, you don’t have to take care of AUTO_inCREMENT value.
When you update id column to make it auto increment, MySQL will automatically set AUTO_INCREMENT value to the MAX + 1 value, event if there are gaps in in column numerotation.
SQL for setting auto increment on id field:
ALTER TABLE `test` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`id`);
Answered By – Alaindeseine
Answer Checked By – Clifford M. (BugsFixing Volunteer)