Error message (Column count doesn't match value count at row 1)

Question

I am currently trying to insert the values in the database table, the database that I have created shows like this:

recipeid - int (Primary key) NOT NULL
recipename - varchar(80)
reciperating - int
recipephoto - longtext

However, when I try to issue the sql statement as shown below:

(insert into userRecipeInfo values('Fried rice',4,'https://i.guim.co.uk/img/media/03734ee186eba543fb3d0e35db2a90a14a5d79e3/0_173_5200_3120/master/5200.jpg?width=1200&height=900')

The error message shows this instead:

Column count doesn't match value count at row 1

I would like to know on what caused this error. Thanks!

score 0 · Answer 1 · answered Sep 22 '20 at 16:14

0

Your INSERT statement provides 3 values, but your table has 4 columns. You need to qualify the target columns - which is highly recommended even if you do provide values for all columns.

insert into userRecipeInfo 
  (recipename, reciperating, recipephoto ) 
values
  ('Fried rice',4,'https://i.guim.co.uk/img/media/03734ee186eba543fb3d0e35db2a90a14a5d79e3/0_173_5200_3120/master/5200.jpg?width=1200&height=900')

answered Sep 22 '20 at 16:14

Hmm, it states "Field 'recipeid' doesn't have a default value" – Jtang11 Sep 22 '20 at 16:23
@Jtang11: well, then you need to provide a value – Sep 22 '20 at 16:42
It states this "Cannot add or update a child row: a foreign key constraint fails (`userinfo`.`userrecipeinfo`, CONSTRAINT `userrecipeinfo_ibfk_1` FOREIGN KEY (`recipeid`) REFERENCES `accountinfo` (`id`))" – Jtang11 Sep 22 '20 at 16:46

score 0 · Answer 2 · answered Sep 22 '20 at 16:15

Because your table has 4 columns, but you only pass 3 values. With an insert statement it is always best to specify the column names:

insert into userRecipeInfo (recipename ,reciperating ,recipephoto ) 
values('Fried rice',4,'https://i.guim.co.uk/img/media/03734ee186eba543fb3d0e35db2a90a14a5d79e3/0_173_5200_3120/master/5200.jpg?width=1200&height=900)

And hope that recipeid gets autogenerated.

The advantage of specifying the column names:

if your schema changes in the future and you add a nullable column, the statement will not break
if the database is recreated and the order of columns change, there is no problem if you have specified the matching column names in the statement.

Error message (Column count doesn't match value count at row 1)

2 Answers2