Re: CREATE TABLE, NOT NULL fields, and empty strings

[Philipp Specht]

Doug Beyer wrote:

create table t1 ( id varchar(5) not null, name varchar(5) not null );
insert into t1 ( id ) values ( "1234" );
select count(*) from t1 where name is null;     	--> Result = 0
select count(*) from t1 where name = "";		--> Result = 1

Questions:
1) Why did the insert succeed since the "name" field is not null and I didn't provide a value?

MySQL uses the default value of this column if you don't provide a value and the column is defined "not null".

2) Why does MySql think it's correct to substitute an empty string for a non-provided value?

http://www.mysql.com/doc/en/CREATE_TABLE.html :

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 string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value (if you haven't explicitly specified another default value with the DEFAULT directive).

Good night,
Philipp


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php