I cannot enter the values in 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','[email protected]','RubenFigueiredo','Cliente');

The mistake you make is this::

Error Code: 1136. Column Count doesn’t match value Count at Row 1

Answer 1

Right Way:

INSERT INTO Utilizadores (IDUtilizador,PNome,UNome,Email,PalavraPasse,TipoUtilizador) VALUES ('', 'Ruben','Figueiredo','[email protected]','RubenFigueiredo','Cliente');

You need it in time to pass the ID, pass it as NULL (''), so that the auto_increment works correctly. This way, it will index the ids

Answer 2

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

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

Option 1

INSERT INTO Utilizadores ( IDUtilizador,PNome,UNome,Email,PalavraPasse,TipoUtilizador) VALUES (null, 'Ruben','Figueiredo','[email protected]','RubenFigueiredo','Cliente');

Option 2

 INSERT INTO Utilizadores (PNome,UNome,Email,PalavraPasse,TipoUtilizador) VALUES ('Ruben','Figueiredo','[email protected]','RubenFigueiredo','Cliente');