[SOLVED] SQL AUTO_INCREMENT starting at number

Issue

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.

Solution

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)

Leave a Reply

Your email address will not be published.