SQL auto increment

Hi Sandra!
Thanks for reaching out.
This is the code structure to use; please apply it to your table and relevant field with its data type, substituting the relevant names in there.

ALTER TABLE table_name MODIFY field_name data_type NOT NULL AUTO_INCREMENT;

Hope this helps.
Best,
Martin

Thanks but I had already tried that way and MySQL sends me this error Error Code: 1833. Cannot change column 'customer_id': used in a foreign key constraint 'sales_ibfk_1' of table 'sales.sales' What should I do?  

Hi Sandra! Can you please paste here the code you see in the DDL tab? Thank you. Looking forward to your answer. Best,
Martin

Of course, this is it: CREATE TABLE `customers` (
`customer_id` int NOT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`gender` enum('M','F') DEFAULT NULL,
`email_address` varchar(255) DEFAULT NULL,
`numer_of_complaints` int DEFAULT '0',
PRIMARY KEY (`customer_id`),
UNIQUE KEY `email_address` (`email_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Hi Sandra! Thanks for providing this code. That's exactly what I was referring to. Therefore, it might be that the DDL of your Sales table can tell us whether the error is not stemming from there.  For instance, here's how my DDL of the 'sales' table looks like.

CREATE TABLE `sales` (
`purchase_number` int(11) NOT NULL AUTO_INCREMENT,
`date_of_purchase` date DEFAULT NULL,
`customer_id` int(11) DEFAULT NULL,
`item_code` varchar(10) DEFAULT NULL,
PRIMARY KEY (`purchase_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Do you think that yours perhaps contains any foreign key constraint related to the customer_id field (something similar is mentioned in the text of error 1833 that you had obtained)? Please make sure that your 'sales' table has been organised in an identical fashion, while altering your 'customers' table. Back to the 'customers' table, I would like to confirm that basically using the code you posted to create the 'customers' table, which is the following one:

CREATE TABLE customers (
customer_id int NOT NULL,
first_name varchar(255) DEFAULT NULL,
last_name varchar(255) DEFAULT NULL,
gender enum('M','F') DEFAULT NULL,
email_address varchar(255) DEFAULT NULL,
numer_of_complaints int DEFAULT 0,
PRIMARY KEY (customer_id),
UNIQUE KEY email_address (email_address)
);

I have managed to set up the 'customers' table in such a way, that the following ALTER TABLE statement did indeed alter that state of the 'customer_id' column in the 'customers' table.

ALTER TABLE customers MODIFY customer_id INT NOT NULL AUTO_INCREMENT;

Here's the DDL of 'customers' after executing the ALTER TABLE Statement.

CREATE TABLE `customers` (
`customer_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`gender` enum('M','F') DEFAULT NULL,
`email_address` varchar(255) DEFAULT NULL,
`numer_of_complaints` int(11) DEFAULT '0',
PRIMARY KEY (`customer_id`),
UNIQUE KEY `email_address` (`email_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Hope this helps but please feel free to get back to us should you need further assistance. Thank you. Best,
Martin