error: "table sales has 6 Columns but 5 values Were supplied"

Question

I created the following table in sql

CREATE TABLE vendas  
    (  
     ID INTEGER IDENTITY(1,1),  
     Empresa VARCHAR (20),  
     Modelo  TEXT,  
     Preco REAL,
     Kilometragem REAL,
     Ano INTEGER,
     PRIMARY KEY(ID)
    );

I put as key the ID column that is with an IDENTITY property

By what I researched, when inserting a new tuple in the table I can omit the ID field because it is auto-generated, however I did the insertion.

INSERT INTO vendas VALUES ("Movidas","RENAULT SANDERO 1.6 EXPRESSION 8V FLEX 4P MANUAL", 36590.0,25.002,2017)

and returns the error

table vendas has 6 columns but 5 values were supplied: INSERT INTO vendas VALUES ("Movidas","RENAULT SANDERO 1.6 EXPRESSION 8V FLEX 4P MANUAL", 36590.0,25.002,2017)

Can someone please help me solve this problem?

Guys, I did as the friend guastallaigor helped me, but now that I made insertions another problem has arisen in the id field. Just going NULL to this field.

Answer 1

When executing the query without specifying the column name, the database has no way of knowing which value should be inserted in which column. In the code:

INSERT INTO vendas VALUES ("Movidas","RENAULT SANDERO 1.6 EXPRESSION 8V FLEX 4P MANUAL", 36590.0,25.002,2017)

It could try to insert "Moved" in the ID column for example.

You can only omit the column name if you have a value for each in the query.

So you need to enter the name of the columns:

Insert into vendas (Empresa,Modelo,Preco,Kilometragem,Ano) values ("Movidas","RENAULT SANDERO 1.6 EXPRESSION 8V FLEX 4P MANUAL", 36590.0,25.002,2017);

Remarks:

• On the field Modelo you are using Text which is recommended only for very large texts as it is stored in the blobs area. Ideally Varchar. (See this answer)

• On the field Ano you are using Integer which can store a very large number (2,147,483,647) which is totally unnecessary. It could be just one NUMERIC(4,0), at least until the year 9999... after that let another programmer solve =]

• About the other fields, do not know which base you are using, also do not know the specification of REAL, then I won’t get into the question.

Answer 2

Try something like this if the ID is incremental in the bank:

INSERT INTO vendas (Empresa, Modelo, Preco, Kilometragem, Ano) VALUES ("Movidas","RENAULT SANDERO 1.6 EXPRESSION 8V FLEX 4P MANUAL", 36590.0,25.002,2017)

Otherwise, try placing an ID manually to see if it works:

INSERT INTO vendas VALUES (1, "Movidas","RENAULT SANDERO 1.6 EXPRESSION 8V FLEX 4P MANUAL", 36590.0,25.002,2017)

EDIT: In case the ID’s were null, probably the AUTOINCREMENT when creating the table vendas in question.

Assuming from the image that the database is Sqlite, it is not possible to perform a ALTER TABLE. This way, if you can the easiest is to drop the whole table (DROP TABLE vendas) and raise her again, something like:

CREATE TABLE vendas  
(  
 ID INTEGER IDENTITY(1,1) AUTOINCREMENT,  
 Empresa VARCHAR (20),  
 Modelo  TEXT,  
 Preco REAL,
 Kilometragem REAL,
 Ano INTEGER,
 PRIMARY KEY(ID)
);

Otherwise a new table should be created:

CREATE TABLE vendasaux  
(  
 ID INTEGER IDENTITY(1,1) AUTOINCREMENT,  
 Empresa VARCHAR (20),  
 Modelo  TEXT,  
 Preco REAL,
 Kilometragem REAL,
 Ano INTEGER,
 PRIMARY KEY(ID)
);

Then insert all records from the table vendas on the table vendasaux:

INSERT INTO vendasaux (Empresa, Modelo, Preco, Kilometragem, Ano) SELECT Empresa, Modelo, Preco, Kilometragem, Ano from vendas;

Then you can now drop the table vendas:

DROP TABLE vendas

And finally, rename the table vendasaux for vendas

ALTER TABLE vendasaux RENAME TO vendas

Obs: I will not be able to test them now, but I believe it will work, any problem send in the comments I assist later.