List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 4 2001 2:19am
Subject:Re: NULL value in VARCHAR fields
View as plain text  
At 8:21 PM +0200 5/3/01, stefan mojschewitsch wrote:
>hi list,
>
>i have a problem with DEFAULT NULL value in a varchar field.
>mysql is version 3.23.33.
>
>i want to have an user field in a row, to see who has created the row.
>
>	create table minidb.tel (
>        	id      int UNSIGNED NOT NULL AUTO_INCREMENT,
>	        tel     char(16) NOT NULL,
>        	service char(1) BINARY,
>        	idate   timestamp(14) NOT NULL,
>       		iuser   varchar(255) NOT NULL,
>        	primary key (id, tel, idate)
>	); 
>
>using DEFAULT USER(), would be nice, but only constants are allowed.
>to prevent inserting rows without iuser, i like to have this field
>with NOT NULL restriction and default value NULL.

That's a contradiction, isn't it?  It doesn't make sense to me.

>
>when inserting a row with
>	insert into tel set tel='+49 174 92932000', service=1;
>
>there will be a record with iuser = ''.
>i thought the meaning of NOT NULL is to reject inserting rows with
>iuser as NULL.

That's right.  But the additional effect, which you may not be aware of,
is that it also changes the default value for the column from NULL
to the empty string.  The practical effect of this for you is that
if you don't name the user in the INSERT statement, the column will
be assigned its default value (as always), but now the default is
the empty string.

If there some reason you're not checking for a missing value yourself?

>
>the line
>		iuser   varchar(255) NOT NULL DEFAULT NULL,
>will rejected with an errormsg.
>
>thx in advance for your help
>
>stefan
>
>--
>st3V13h
>
>nach diktat verreist


-- 
Paul DuBois, paul@stripped
Thread
problems with privileges, MySQL for Windows 3.23.xxMySQL-Mail3 May
  • NULL value in VARCHAR fieldsstefan mojschewitsch3 May
    • Re: NULL value in VARCHAR fieldsPaul DuBois4 May
RE: NULL value in VARCHAR fieldsPaul DuBois4 May