Answer for SQL auto increment

https://365datascience.com/dwqa-answer/answer-for-sql-auto-increment-5/ -

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

#365datascience #DataScience #data #science #365datascience #BigData #tutorial #infographic #career #salary #education #howto #scientist #engineer #course #engineer #MachineLearning #machine #learning #certificate #udemy