mysql – I can't insert the values ​​into the table with auto_increment in the primary key

Question:

This is my table:

CREATE TABLE Utilizadores(
IDUtilizador INT NOT NULL AUTO_INCREMENT,
PNome VARCHAR(2000) NOT NULL,
UNome VARCHAR(2000) NOT NULL,
Email VARCHAR(2000) NOT NULL,
PalavraPasse VARCHAR(2000) NOT NULL,
TipoUtilizador VARCHAR(2000) NOT NULL,
Check(TipoUtilizador='Administrador' OR TipoUtilizador='Cliente'),
PRIMARY KEY(IDUtilizador)
);

This is my Insert:

INSERT INTO Utilizadores (IDUtilizador,PNome,UNome,Email,PalavraPasse,TipoUtilizador) VALUES ('Ruben','Figueiredo','RubenFigueiredo@gmail.com','RubenFigueiredo','Cliente');

the error that gives me is the following:

Error Code: 1136. Column count doesn't match value count at row 1

Answer:

In the auto-increment column you can omit it from the field list and from the VALUES clause or pass null as a value.

Passing an empty string can work depending on the setting of NO_AUTO_VALUE_ON_ZERO which, if active, will allow zero value in the column and when finding the empty string it will try to insert the record always with zero which causes the primary key duplication error, in this case to have correct behavior just null increments the value.

Option 1

INSERT INTO Utilizadores ( IDUtilizador,PNome,UNome,Email,PalavraPasse,TipoUtilizador) VALUES (null, 'Ruben','Figueiredo','RubenFigueiredo@gmail.com','RubenFigueiredo','Cliente');

Option 2

 INSERT INTO Utilizadores (PNome,UNome,Email,PalavraPasse,TipoUtilizador) VALUES ('Ruben','Figueiredo','RubenFigueiredo@gmail.com','RubenFigueiredo','Cliente');