cannot set DEFAULT with NOT NULL in batch processing

Entschuldigt mein englisches Keyboard. Folgendes Problem (Newbie):

MySQL 5.0.20-nt under Windows XP.

I'm executing the following batch file from the command line with
C:\> mysql
=======================
use testdb1;
-- DROP TABLE tblSamples; -- Clear data from previous run
CREATE TABLE tblSamples
(Number INTEGER PRIMARY KEY,
Name VARCHAR(32) NOT NULL DEFAULT 'SER' UNIQUE,
Comment TEXT
);
INSERT INTO tblSamples VALUES
('1','PLA',NULL),
('2','','Howdy comment'),
-- ('3',NULL,'Howdy comment'),
('4','RFA',NULL);

-- print to verify
SELECT * FROM tblSamples;
======================

Problem: I would like to have the default, SER, inserted in Name.
However, NULL will be rejected due to the NOT NULL constraint, but
entering an empty string will override the default. (Therefore, I had
to out-comment row 3.)

Is it that I do not understand something fundamental about how to
script for MySQL, or am I trying something quite meaningless and
defaults do not work together with batch processing under NOT NULL
restriction at all?

Tausend Dank -- Armin

Re: cannot set DEFAULT with NOT NULL in batch processing

"Cucumber" wrote:

> CREATE TABLE tblSamples
> (Number INTEGER PRIMARY KEY,
> Name VARCHAR(32) NOT NULL DEFAULT 'SER' UNIQUE,
> Comment TEXT
> );

> INSERT INTO tblSamples VALUES
> ('1','PLA',NULL),
> ('2','','Howdy comment'),
> -- ('3',NULL,'Howdy comment'),
> ('4','RFA',NULL);

> Problem: I would like to have the default, SER, inserted in Name.
> However, NULL will be rejected due to the NOT NULL constraint, but
> entering an empty string will override the default.

Der Defaultwert wird benutzt, wenn du beim INSERT *keinen* Wert für die
Spalte angibst. Der bevorzugte Weg [1] wäre, komplette INSERTs - mit
Spaltenliste - schreiben:

INSERT INTO tblSamples (Number, Comment)
VALUES (42, 'Überraschung!')

Alternativ kannst du in der Werteliste das Schlüsselwort DEFAULT
verwenden oder INSERT ... SET ... schreiben.

RTFM: http://dev.mysql.com/doc/refman/5.0/en/insert.html


[1] INSERT INTO