| Package home | Report new bug | New search | Development Roadmap | Status: Open | Feedback | All | Closed Since Version 0.8.6 |
| Request #8074 | Column Defaults | ||
|---|---|---|---|
| Submitted: | 2006-06-29 15:01 UTC | ||
| From: | michael at apison dot com | Assigned: | lsmith |
| Status: | Closed | Package: | MDB2_Schema (version 0.5.0) |
| PHP Version: | 5.1.4 | OS: | irrelevant |
| Roadmaps: | (Not assigned) | ||
[2006-06-29 15:01 UTC] michael at apison dot com
(Michael Caplan)
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
Comments
[2006-06-29 15:04 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[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:28 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[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 17:37 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[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?
[2006-06-29 18:14 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2006-06-30 10:11 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2006-08-11 19:46 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2006-08-11 23:38 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2006-08-12 11:23 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2006-08-12 17:35 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2006-08-12 17:39 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!