user222368 user222368 - 4 months ago 7
MySQL Question

ERROR 1054 (42S22): Unknown column 'marks' in 'field list'

This is a very simple MySQL query.

INSERT INTO users_questions (user_id, question_id, mcopt_id,timestamp)
VALUES (50053, 875, 3092, '2015-08-22 18:01:44');


When I use it I get


ERROR 1054 (42S22): Unknown column 'marks' in 'field list'


marks
is a column in the same table whose default value is set to NULL and in the above query I don't even use the column name
marks
.

So why exactly am i getting the error?

Structure of table:

+-------------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+-------------------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| question_id | int(11) | NO | PRI | NULL | |
| mcopt_id | int(11) | NO | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
| marks | int(11) | NO | | NULL | |
+-------------+-----------+------+-----+-------------------+-------+


Just to make it clear I also get the error when I provide the value of
marks


INSERT INTO users_questions (user_id, question_id, mcopt_id, timestamp, marks) VALUES (50053, 875, 3094, '2015-08-22 19:15:07', 1)


`

Answer

A:

create table users_questions2
(   user_id int not null,
    question_id int not null,
    mcopt_id int not null,
    timestamp timestamp not null,
    marks int not null
);
describe  users_questions2;
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field       | Type      | Null | Key | Default           | Extra                       |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| user_id     | int(11)   | NO   |     | NULL              |                             |
| question_id | int(11)   | NO   |     | NULL              |                             |
| mcopt_id    | int(11)   | NO   |     | NULL              |                             |
| timestamp   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| marks       | int(11)   | NO   |     | NULL              |                             |
+-------------+-----------+------+-----+-------------------+-----------------------------+

INSERT INTO users_questions2 (user_id, question_id, mcopt_id, timestamp) VALUES (50053, 875, 3092, '2015-08-22 18:01:44');

Error Code: 1364. Field 'marks' doesn't have a default value    0.047 sec

INSERT INTO users_questions2 (user_id, question_id, mcopt_id, timestamp,marks) VALUES (50053, 875, 3092, '2015-08-22 18:01:44',1);
-- 1 row(s) affected

INSERT INTO users_questions2 (user_id, question_id, mcopt_id, timestamp,marks) VALUES (50053, 875, 3092, '2015-08-22 18:01:44',null);
Error Code: 1048. Column 'marks' cannot be null 0.000 sec

B:

drop table users_questions2;

create table users_questions2
(   user_id int  null,
    question_id int  null,
    mcopt_id int  null,
    timestamp timestamp  null,
    marks int  null
);
describe  users_questions2;
+-------------+-----------+------+-----+---------+-------+
| Field       | Type      | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| user_id     | int(11)   | YES  |     | NULL    |       |
| question_id | int(11)   | YES  |     | NULL    |       |
| mcopt_id    | int(11)   | YES  |     | NULL    |       |
| timestamp   | timestamp | YES  |     | NULL    |       |
| marks       | int(11)   | YES  |     | NULL    |       |
+-------------+-----------+------+-----+---------+-------+
INSERT INTO users_questions2 (user_id, question_id, mcopt_id, timestamp) VALUES (50053, 875, 3092, '2015-08-22 18:01:44');

1 row(s) affected

So the only way I can get my describe table to look like yours is if they are not null columns (section A above). Which means your columns do not accept nulls.

Edit:

show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.24                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.24-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
Comments