List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 1 2001 2:41am
Subject:Re: ENUM NOT NULL without default value
View as plain text  
At 2:43 AM +0100 10/1/01, Corin Hartland-Swann wrote:
>Hi Monty,
>
>I've been experimenting with ENUM NOT NULL columns without a default
>value. The documentation (section 6.5.3 again) states:
>
>"If no DEFAULT value is specified for a column, MySQL automatically
>assigns one. If the column may take NULL as a value, the default value is
>NULL. If the column is declared as NOT NULL, the default value depends on
>the column type ... for ENUM, the default is the first enumeration value."
>
>This is all fine (and seems like a reasonable assumption to make), but I
>discovered that using ALTER TABLE on the column to DROP DEFAULT does
>nothing - but that SET DEFAULT '' does remove it.
>
>Would it be possible to make DROP DEFAULT set the default to '' on an
>ENUM NOT NULL column?

That would be incorrect.

DROP DEFAULT drops whatever default is currently assigned, and then MySQL
assigns the default value that it would otherwise automatically define.
For a NOT NULL enumeration column, that's the first enumeration value,
according to the passage you quote above.

Why introduce a change to DROP DEFAULT that makes it inconsistent
with its current behavior, particularly when you can just use
SET DEFAULT to set the default to '' if that's what you want?

>Here's an example of what I'm talking about:
>
>mysql> CREATE TABLE test_enum (e ENUM('a','b','c') NOT NULL);
>mysql> DESCRIBE test_enum;
>+-------+-------------------+------+-----+---------+-------+
>| Field | Type              | Null | Key | Default | Extra |
>+-------+-------------------+------+-----+---------+-------+
>| e     | enum('a','b','c') |      |     | a       |       |
>+-------+-------------------+------+-----+---------+-------+
>
>mysql> ALTER TABLE test_enum ALTER COLUMN e DROP DEFAULT;
>mysql> DESCRIBE test_enum;
>+-------+-------------------+------+-----+---------+-------+
>| Field | Type              | Null | Key | Default | Extra |
>+-------+-------------------+------+-----+---------+-------+
>| e     | enum('a','b','c') |      |     | a       |       |
>+-------+-------------------+------+-----+---------+-------+
>
>mysql> ALTER TABLE test_enum ALTER COLUMN e SET DEFAULT '';
>mysql> DESCRIBE test_enum;
>+-------+-------------------+------+-----+---------+-------+
>| Field | Type              | Null | Key | Default | Extra |
>+-------+-------------------+------+-----+---------+-------+
>| e     | enum('a','b','c') |      |     |         |       |
>+-------+-------------------+------+-----+---------+-------+
>
>Thanks,
>
>Corin
>
>/------------------------+-------------------------------------\
>| Corin Hartland-Swann   |    Tel: +44 (0) 20 7491 2000        |
>| Commerce Internet Ltd  |    Fax: +44 (0) 20 7491 2010        |
>| 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027        |
>| Gilbert Street         |                                     |
>| Mayfair                |    Web: http://www.commerce.uk.net/ |
>| London W1K 5HJ         | E-Mail: cdhs@stripped        |
>\------------------------+-------------------------------------/





-- 
Paul DuBois, paul@stripped
Thread
ENUM NOT NULL without default valueCorin Hartland-Swann1 Oct
Re: ENUM NOT NULL without default valuePaul DuBois1 Oct