MySQL reject insert `NULL` value in `default null` column

Question

MySQL reject insert NULL value in default null column: (column: tid)

Table structure:

CREATE TABLE `ww_uid_tid_qid_aid` (
  `id` int(11) NOT NULL,
  `qzid` int(11) NOT NULL,
  `uid` int(32) NOT NULL,
  `tid` int(11) DEFAULT NULL,
  `qid` int(11) NOT NULL,
  `aid` int(11) DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `time` decimal(11,2) NOT NULL,
  `wokbits` int(11) NOT NULL,
  `create_date` datetime NOT NULL,
  `update_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `ww_uid_tid_qid_aid`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `qzid_2` (`qzid`,`uid`,`qid`) USING BTREE,
  ADD KEY `ww_uid_tid_qid_aid.tid` (`tid`),
  ADD KEY `qid` (`qid`),
  ADD KEY `aid` (`aid`),
  ADD KEY `uid` (`uid`),
  ADD KEY `qzid` (`qzid`),
  ADD KEY `qid_2` (`qid`,`aid`),
  ADD KEY `status` (`status`),
  ADD KEY `status_2` (`status`,`aid`),
  ADD KEY `qzid_3` (`qzid`,`qid`),
  ADD KEY `wokbits` (`wokbits`),
  ADD KEY `tid` (`tid`,`status`);

SQL Query:

insert ignore into ww_uid_tid_qid_aid
(id,qzid,uid,tid,qid,aid,status,time,wokbits,create_date,update_date)
values
(142598981,1000110,10006849,NULL,10237838,10237840,1,3.4032369852066,44,now(),now())

Error msg:

#1048 - Column 'tid' cannot be null

Also tried: DEFAULT

insert ignore into ww_uid_tid_qid_aid
(id,qzid,uid,tid,qid,aid,status,time,wokbits,create_date,update_date)
values
(142598981,1000110,10006849,DEFAULT,10237838,10237840,1,3.4032369852066,44,now(),now())

Return same Error msg,

Edit:

table is large, contain 587,702 row

foreign keys:

ALTER TABLE `ww_uid_tid_qid_aid`
  ADD CONSTRAINT `ww_uid_tid_qid_aid_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `tag` (`id`),
  ADD CONSTRAINT `ww_uid_tid_qid_aid_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`id`),
  ADD CONSTRAINT `ww_uid_tid_qid_aid_ibfk_3` FOREIGN KEY (`qid`) REFERENCES `question` (`id`),
  ADD CONSTRAINT `ww_uid_tid_qid_aid_ibfk_4` FOREIGN KEY (`qzid`) REFERENCES `ww_quiz` (`id`),
  ADD CONSTRAINT `ww_uid_tid_qid_aid_ibfk_5` FOREIGN KEY (`qid`,`aid`) REFERENCES `answer_choice` (`question_id`, `id`),
  ADD CONSTRAINT `ww_uid_tid_qid_aid_ibfk_6` FOREIGN KEY (`status`,`aid`) REFERENCES `answer_choice` (`is_correct`, `id`),
  ADD CONSTRAINT `ww_uid_tid_qid_aid_ibfk_7` FOREIGN KEY (`qzid`,`qid`) REFERENCES `ww_question` (`qzid`, `qid`);

As an aside, note that you have no PRIMARY KEY, which may prove problematic further down the road. — Strawberry
– Strawberry, Commented Jul 3, 2016 at 8:35
try not setting a default in create query because its anyway its defaults to null — Malinga
– Malinga, Commented Jul 3, 2016 at 8:37
One of reason may be the data file corrupted. To test it create another table like it and insert all rows to that. (You can use 'create t2 like t1' syntax). Then insert this row to that table. — Mostafa Vatanpour
– Mostafa Vatanpour, Commented Jul 3, 2016 at 9:12

Bernd Buffen · Accepted Answer · 2016-07-03 08:41:57Z

2

You not have defined that tid can be NULL. change the CREATE TABLE to:

CREATE TABLE `ww_uid_tid_qid_aid` (
  `id` int(11) NOT NULL,
  `qzid` int(11) NOT NULL,
  `uid` int(32) NOT NULL,
  `tid` int(11) NULL DEFAULT NULL,
  `qid` int(11) NOT NULL,
  `aid` int(11) DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `time` decimal(11,2) NOT NULL,
  `wokbits` int(11) NOT NULL,
  `create_date` datetime NOT NULL,
  `update_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT IGNORE INTO ww_uid_tid_qid_aid
(id,qzid,uid,tid,qid,aid,STATUS,TIME,wokbits,create_date,update_date)
VALUES
(142598981,1000110,10006849,NULL,10237838,10237840,1,3.4032369852066,44,now(),now());

sample to alter the table

ALTER TABLE  `ww_uid_tid_qid_aid`
MODIFY COLUMN `tid`  INT(11) NULL DEFAULT NULL;

EXAMPLE with ALTER TABLE and INSERT

MariaDB [yourschema]> CREATE TABLE `ww_uid_tid_qid_aid` (
    ->   `id` int(11) NOT NULL,
    ->   `qzid` int(11) NOT NULL,
    ->   `uid` int(32) NOT NULL,
    ->   `tid` int(11) DEFAULT NULL,
    ->   `qid` int(11) NOT NULL,
    ->   `aid` int(11) DEFAULT NULL,
    ->   `status` tinyint(1) DEFAULT NULL,
    ->   `time` decimal(11,2) NOT NULL,
    ->   `wokbits` int(11) NOT NULL,
    ->   `create_date` datetime NOT NULL,
    ->   `update_date` datetime NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.47 sec)

MariaDB [yourschema]>
MariaDB [yourschema]> ALTER TABLE  `ww_uid_tid_qid_aid`
    -> MODIFY COLUMN `tid`  INT(11) NULL DEFAULT NULL;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [yourschema]>
MariaDB [yourschema]> INSERT IGNORE INTO ww_uid_tid_qid_aid
    -> (id,qzid,uid,tid,qid,aid,STATUS,TIME,wokbits,create_date,update_date)
    -> VALUES
    -> (142598981,1000110,10006849,NULL,10237838,10237840,1,3.4032369852066,44,now(),now())
    -> ;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [yourschema]> SELECT * FROM ww_uid_tid_qid_aid;
+-----------+---------+----------+------+----------+----------+--------+------+---------+---------------------+---------------------+
| id        | qzid    | uid      | tid  | qid      | aid      | status | time | wokbits | create_date         | update_date         |
+-----------+---------+----------+------+----------+----------+--------+------+---------+---------------------+---------------------+
| 142598981 | 1000110 | 10006849 | NULL | 10237838 | 10237840 |      1 | 3.40 |      44 | 2016-07-03 10:40:38 | 2016-07-03 10:40:38 |
+-----------+---------+----------+------+----------+----------+--------+------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

MariaDB [yourschema]>

edited Jul 3, 2016 at 8:41

answered Jul 3, 2016 at 8:33

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bernd Buffen Over a year ago

@mwafi - i have add it in my answer

Ted Over a year ago

Same issue after run: ALTER TABLE ww_uid_tid_qid_aid MODIFY COLUMN tid INT(11) NULL DEFAULT NULL;

Bernd Buffen Over a year ago

@mwafi - i not understand that. i have add a example of all

Ted Over a year ago

It's large table, could it be the issue: row# 587,702 ?

Strawberry Over a year ago

So Bernd, are you able to replicate the behaviour that the OP describes?

Ted · Accepted Answer · 2016-07-03 09:54:52Z

1

Solved after applied 'Msfvtp' solution:

CREATE TABLE ww_uid_tid_qid_aid_tmp LIKE ww_uid_tid_qid_aid;
INSERT INTO ww_uid_tid_qid_aid_tmp SELECT * FROM ww_uid_tid_qid_aid;
RENAME TABLE ww_uid_tid_qid_aid TO ww_uid_tid_qid_aid_tmp2, ww_uid_tid_qid_aid_tmp To ww_uid_tid_qid_aid;

SQL query:

insert ignore into ww_uid_tid_qid_aid
(id,qzid,uid,tid,qid,aid,status,time,wokbits,create_date,update_date)
values
(142598981,1000110,10006849,DEFAULT,10237838,10237840,1,3.4032369852066,44,now(),now())

work fine,

answered Jul 3, 2016 at 9:54

Ted

4,1868 gold badges63 silver badges88 bronze badges

1 Comment

Mostafa Vatanpour Over a year ago

Glade I could help :)

Shaharyar · Accepted Answer · 2016-07-03 08:35:23Z

0

There is a difference in both statements:

Allow NULL:

Column will store NULL as you're trying to do.

DEFAULT NULL:

If you want to store NULL, simply don't pass the value of that column.

Now just alter your table:

ALTER TABLE tablename MODIFY tid int(11);

It will be NULL when you don't pass the value. Because all columns are Nullable by default.

answered Jul 3, 2016 at 8:35

Shaharyar

12.5k4 gold badges50 silver badges70 bronze badges

Collectives™ on Stack Overflow

MySQL reject insert `NULL` value in `default null` column

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related