CODEWITHSUNDEEP
mysql
Imtango30
Imtango30

Reputation: 143

Id is not been auto increment id in mysql

I am using MySQL on localhost. I have created a table name students. I have set id as NOT NULL AUTO_INCREMENT unique. But if I add data in table, I have to mention id in the data too. If I don't mention id, an error shows. Following is the code I used to create table.

CREATE TABLE students 
(
      id INT NOT NULL AUTO_INCREMENT unique,
      name VARCHAR(100) NOT NULL,
      email VARCHAR(150) NOT NULL unique,
      age tinyint NOT NULL,
      password VARCHAR(100) NOT NULL,
      phone_number VARCHAR(150) NOT NULL unique,
      address text,
      gender enum ("M", "F", "O"),
      status boolean default 1
    )

In the table, I am adding following data

INSERT INTO students
VALUES 
("Liam", "[email protected]", 20, 123456789, 987654321, "330 Toy Flat Suite 210", "M"),
("Noah", "[email protected]", 25, 234567891, 876543219, "1240 Dooley Shoals", "M"),
("Oliver", "[email protected]", 20, 345678912, 765432198, "922 Elody Neck", "F"),
("Elijah", "[email protected]", 25, 456789123, 654321987, "1704 Mertie Gardens Suite 328", "F"),
("James", "[email protected]", 30, 567891234, 543219876, "46388 Herman Crossing", "F"),
("William", "[email protected]", 30, 678912345, 432198765, "7259 Hubert Mission Suite 338", "F"),
("Benjamin", "[email protected]", 31, 789123456, 321987654, "57126 Ramiro Summit", "M")

If I run this code, an error shows.

Even if I add column name "(name, email, age, password, phone_number, address, gender)" before value.

Data do not enter in the table. But if I mention Id, at first, mysql saves the data.

Upvotes: 0

Views: 815

Answers (1)

Barmar
Barmar

Reputation: 780688

When you're inserting, you have to either specify the list of columns you're giving values to, or provide values for all columns. It's best to have an explicit list of columns, so you're not dependent on the order in the table definition. This also allows you to leave out columns that should get defaults.

INSERT INTO students (name, email, age, password, phone_number, address, gender) VALUES
("Liam", "[email protected]", 20, '123456789', '987654321', "330 Toy Flat Suite 210", "M"),
("Noah", "[email protected]", 25, '234567891', '876543219', "1240 Dooley Shoals", "M"),
("Oliver", "[email protected]", 20, '345678912', '765432198', "922 Elody Neck", "F"),
("Elijah", "[email protected]", 25, '456789123', '654321987', "1704 Mertie Gardens Suite 328", "F"),
("James", "[email protected]", 30, '567891234', '543219876', "46388 Herman Crossing", "F"),
("William", "[email protected]", 30, '678912345', '432198765', "7259 Hubert Mission Suite 338", "F"),
("Benjamin", "[email protected]", 31, '789123456', '321987654', "57126 Ramiro Summit", "M");

If you don't list the column names, you have to provide values for all columns, even the ones that have default values. You can use NULL for the auto increment ID column and it will get the incremented values. You can use the keyword DEFAULT for columns like status, so they get their default value.

Upvotes: 3

Related Questions