Re: Regarding NULL and '' (null string) treatment in MYSQL - Peter Brawley - com.mysql.lists.mysql

3 messages in com.mysql.lists.mysqlRe: Regarding NULL and '' (null strin...

From	Sent On	Attachments
laks...@wipro.com	29 Mar 2005 19:47
Peter Brawley	29 Mar 2005 21:24
Shankar Unni	30 Mar 2005 10:34

Subject:	Re: Regarding NULL and '' (null string) treatment in MYSQL
From:	Peter Brawley (pete...@earthlink.net)
Date:	03/29/2005 09:24:47 PM
List:	com.mysql.lists.mysql

Narasimha,

In programming languages, a 'null string' is empty, but in ANSI SQL, NULL means unknown, _not_ empty, so in ANSI SQL, NULLs are never equal to anything, not even themselves: the expressions NULL=NULL, NULL<>NULL and NULL='' all evaluate to NULL.

Microsoft SQL has an 'ansi_nulls' setting which turns off this feature. With ansi_nulls turned off, NULL works as you want it to (thus breaking a lot of traditional SQL code). Perhaps Oracle also has such a setting too. MySQL doesn't.

Referential integrity (RI) is meant to work oppositely to your description: absent a matching key value in the parent table, a child row cannot be inserted. What you describe looks like what's often called the 'zeroth row' workaround: create a parent row with an empty key value, then add matching child rows. It wrecks RI. If you absolutely must add child rows before adding the parent row, use empty values, not NULLs, but most DBAs would insist that you to revise the design such that empty parent key values are not permitted.

Peter Brawley http://www.artfulsoftware.com

-----

laks...@wipro.com wrote:

Hi,

MySQL treats NULL, '' (empty string) as different.

I mean when I select from/insert into a table, its behaviour is different.

Select * from table1 where name='';

is different from

Select * from table1 where name=NULL;

Similarly

Insert into table1(name) values('') is different from

Insert into table1(name) values(NULL)

Please suggest me here, how to overcome this '' (null string) problem.

Scenario:

I have two tables, one is parent and one is child.

Child is referencing 3 fields in parent table.

Parent table records are empty. Now I am trying to insert into the child table with '' (null string) values into these 3 fields (which are referencing to the parent table). Actually insert into the child table in this scenario should be successful, but I am not able to make it success because of the problem described above ( Treatment of NULL, '' are different).

Note: If the parent is not having any records, we can insert into the child. BCS, in this case referential integrity won't work.

Ps: In oracle, it is not the case it treats NULL,'' as same.

Please suggest me how to proceed here.

Thanks, Narasimha

Confidentiality Notice