4 messages in com.mysql.lists.win32RE: A NOT NULL field can be set to an...
FromSent OnAttachments
Glenn F. Henriksen13 Jun 2001 06:17 
Paul DuBois13 Jun 2001 17:47 
Luc Van der Veken14 Jun 2001 00:29 
Tim Endres14 Jun 2001 10:04 
Subject:RE: A NOT NULL field can be set to and empty string
From:Tim Endres (Tim.@SecureByDesign.com)
Date:06/14/2001 10:04:24 AM
List:com.mysql.lists.win32

Some of this confusion is probably due to the fact that some databases, namely Oracle, treat the empty string and NULL as equivalent. This is one of the reasons I dislike Oracle.

tim.

Look at it as objects: NULL means that there really is no object, but an empty string is an object (it has a type: string, and a size, be it that that size is zero). Disallowing empty strings means that your own app will have to do some kind of validity check (on a minimum length) before accepting the data for transmission to the server.

-----Original Message----- From: Glenn F. Henriksen [mailto:gle@itl.no] Sent: Wednesday 13 June 2001 15:18 To: win@lists.mysql.com Subject: A NOT NULL field can be set to and empty string

Hello.

I just noticed that when I have a table that has a field created with NOT NULL (in the example I'm using "clientID" I can set it to NULL with no error but it is set to an empty string ( '' ).

This is obviously by design: From the manual chapter 7.7 Create Table syntax: "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. (...)"

I found it very confusing that it allowed an empty field when I had specified "NOT NULL".

Is there any other way to disallow empty fields?

Thank you.

Regards, Glenn Henriksen (gle@henriksen.no)

Example:

CREATE TABLE `client` (`clientID` varchar(36) binary NOT NULL default '0', `clientName` varchar(250) NOT NULL default '0', PRIMARY KEY (`clientID`), UNIQUE KEY `clientID`(`clientID`), ) TYPE=MyISAM COMMENT=''

mysql> select * from client; +--------------------------------------+-------------+ | clientID | clientName | +--------------------------------------+-------------+ | | Demo Client | | 1ddefdb7-2648-4fc5-b052-6fc9cbb3959e | Test client | +--------------------------------------+-------------+ 2 rows in set (0.00 sec)

mysql> select * from client where clientID is null; Empty set (0.03 sec)

mysql> update client set clientID=null where clientName="Demo Client"; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1

mysql> select * from client; +--------------------------------------+-------------+ | clientID | clientName | +--------------------------------------+-------------+ | | Demo Client | | 1ddefdb7-2648-4fc5-b052-6fc9cbb3959e | Test client | +--------------------------------------+-------------+ 2 rows in set (0.00 sec)

mysql> select * from client where clientID is null; Empty set (0.00 sec)

mysql> select * from client where clientID = ''; +----------+-------------+ | clientID | clientName | +----------+-------------+ | | Demo Client | +----------+-------------+ 1 row in set (0.00 sec)

mysql>

--------------------------------------------------------------------- Please check

"http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail win3@lists.mysql.com

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail win3@lists.mysql.com instead.

--------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail win3@lists.mysql.com

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail win3@lists.mysql.com instead.