[Typo3-dev] NOT NULL for some columns in tt_address

[Karsten Dambekalns]

Hi Dimitri.

On 2004-12-14, Dimitri Tarassenko <mitka at mitka.us> wrote:
>> Their notion of NOT NULL is somewhat crude, so the
>> above makes perfect sense to them - the default is an empty string.
>
> I'd say it's rather peculiar. So, a NOT NULL field always has an 
> implicit default defined?

Yes. From http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

 ---- snip ----
Prior to MySQL 5.0.2, if a column definition includes no explicit
DEFAULT value, MySQL determines the default value as follows: If the
column can take NULL as a value, the column is defined with an
explicit DEFAULT NULL clause. If the column cannot take NULL as the
value, MySQL defines the column with an explicit DEFAULT clause, using
the implicit default value for the column data type. Implicit defaults
are defined as follows:

    * For numeric types other than those declared with the
    AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT
    column, the default value is the next value in the sequence.
    * For date and time types other than TIMESTAMP, the default is the
    appropriate ``zero'' value for the type. For the first TIMESTAMP
    column in a table, the default value is the current date and
    time. See section 11.3 Date and Time Types.
    * For string types other than ENUM, the default value is the empty
    string. For ENUM, the default is the first enumeration value. 
 ---- snip ----

>> In fact, NOT NULL in MySQL *always* allows you to insert without
>> specifiyng the field, it just means the field will not be NULL

>From the above quoted text, and from what follows on the original
page, this is different from MySQL 5.0.2 onwards - at least of strict
mode is enabled:

 ---- snip ----
For data entry, if an INSERT or REPLACE statement includes no value
for the column, MySQL handles the column according to to the SQL mode
in effect at the time:

    * If strict mode is not enabled, MySQL sets the column to the
    implicit value for the column data type.
    * If strict mode is enabled, an error occurs for transactional
    tables and the statement is rolled back. For non-transactional
    tables, an error occurs unless the row is the second or subsequent
    row of a multiple-row statement, in which case a warning occurs. 
 ---- snip ----

> Interesting. The problem that I have encountered is with a third-party 
> MySQL client that apparently pre-validates the data based on the 
> database scheme before attempting to insert - EMS MySQL manager. When 
> trying to add a record there it prompts me for these three fields.

Oh - interesting.

> Oh well, the wonderful world of MySQL :)))

Yes. I had a lot of things to think about when really diving into
it... ;)

Karsten