Home » Database » MDB2_Schema » Bug #8074

[2006-06-29 15:01 UTC] michael at apison dot com

Description:
------------
Hi there,

Why MDB2_Schema insists on defining a default value (should a default not be supplied) for columns that are marked NOT NULL? Text columns are treated of having a default value of ' ', and integers of having a default value of 0.

For example, I have the following table definition:

<table>
<name>shop_cat_products_attrib</name>
<declaration>
<field>
<name>product_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
</field>
<field>
<name>locale</name>
<type>text</type>
<length>5</length>
<notnull>true</notnull>
</field>
<field>
<name>attrib_key</name>
<type>text</type>
<length>255</length>
<notnull>true</notnull>
</field>
<field>
<name>attrib_value</name>
<type>text</type>
<notnull>true</notnull>
</field>
<index>
<name>primary</name>
<primary>true</primary>
<field>
<name>product_id</name>
</field>
<field>
<name>locale</name>
</field>
<field>
<name>attrib_key</name>
</field>
</index>
<index>
<name>values_idx</name>
<field>
<name>attrib_value</name>
</field>
</index>
</declaration>
</table>

which creates the following DDL using the MySQLi driver:

CREATE TABLE shop_cat_categories_attrib (
category_id INT UNSIGNED DEFAULT 0 NOT NULL,
locale VARCHAR(5) DEFAULT ' ' NOT NULL,
attrib_key VARCHAR(255) DEFAULT ' ' NOT NULL,
attrib_value TEXT DEFAULT ' ' NOT NULL
) ENGINE = INNODB

This fails because a LOB cannot have a default value with MySQL (Native message: BLOB/TEXT column 'attrib_value' can't have a default value). Why not treat the absence of default values just as that?

CREATE TABLE shop_cat_categories_attrib (
category_id INT UNSIGNED NOT NULL,
locale VARCHAR(5) NOT NULL,
attrib_key VARCHAR(255) NOT NULL,
attrib_value TEXT NOT NULL
) ENGINE = INNODB

[2006-06-29 15:35 UTC] michael at apison dot com

Okay, thanks for the tip re: clobs. I still don't get why, lobs aside, a not null column without a defined default gets a default assigned. Using the same DB definition, with the change made to the attrib_value column as suggested, I'm still getting defaults defined for all other NOT NULL columns:

CREATE TABLE `shop_cat_categories_attrib` (
`category_id` int(10) unsigned NOT NULL default '0',
`locale` varchar(5) NOT NULL default ' ',
`attrib_key` varchar(255) NOT NULL default ' ',
`attrib_value` longtext NOT NULL,
PRIMARY KEY (`category_id`,`locale`,`attrib_key`),
KEY `values_idx_idx` (`attrib_value`(767))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

[2006-06-29 16:58 UTC] michael at apison dot com

Hey Lukas,

Thanks for the further elaboration. As for NOT NULL columns needing a default, that may be the case for certain DBs, but isn't the case with all (MySQL, for example). Providing a default value to a NOT NULL column (unless the default is "legitimate") defeats the purpose of having a NOT NULL setting. Should not an INSERT statement fail if I neglected to include all required column data, or should the database stuff the record with '' or 0?

Am I missing something here?

[2006-06-29 18:04 UTC] michael at apison dot com

Gotcha. thanks for the clarification.

The issue I see with setting unwarranted defaults is data integrity. If I define a NOT NULL column, I expect that each record has valid data set for that column. A '' or 0 may not constitute validate data. For that matter, a default value of any description may not consititurte valid data, as it should be explicitly set by the end user with a valid INSERT statement. Having the INSERT statement die when a NOT NULL column is not set would be a desirable outcome indicating that not all the data requirements have been met.

DB portability wise, I really have no clue how this figures into the grand universe. What DBs use an implicit default for NOT NULL columns?