Bug #20532 setting DEFAULT value in CREATE TABLE sql not working

• Submitted: 19 Jun 2006 11:36
• Modified: 19 Jun 2006 12:43
• Reporter: dodo
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.22
• OS: Windows (winxp HOME)
• CPU Architecture: Any

[19 Jun 2006 11:36] dodo

Description:
I am using MySQL Server 5.0.22 on winXP.

CREATE TABLE  sql has problem setting DEFAULT value
in column.

How to repeat:
I can created the following simple table

CREATE TABLE category (
  category_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_category_id int(10) NOT NULL DEFAULT '0',
  name VARCHAR(255) NOT NULL,
  created_time DATETIME ,
  description VARCHAR(1000),
  PRIMARY KEY(category_id),
  CONSTRAINT UNIQUE(parent_category_id, name)
)TYPE=InnoDB;

Note column parent_category_id here, it has DEFAULT '0'.

NOw when I try to insert a record with null for parent_category_id,
it won't put a default value of 0 for parent_category_id.

E.g. insert sql

insert into category(category_id, parent_category_id, name, description, created_time) values (null, null , "Junk", "trash", null);

Suggested fix:

skip parent_category_id column

insert into category(category_id, name, description, created_time) values (null, "Junk", "trash", null)

[19 Jun 2006 12:43] MySQL Verification Team

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

mysql> insert into category(category_id, parent_category_id, name, description,
    -> created_time) values (null, null , "Junk", "trash", null);
ERROR 1048 (23000): Column 'parent_category_id' cannot be null

You are trying to insert a NULL value for a column defined NOT NULL:

parent_category_id int(10) NOT NULL DEFAULT '0',

insert into category(category_id, parent_category_id, name, description,
created_time) values (null, null , "Junk", "trash", null);
                                      ^^^^