Re: Foreign key in Oracle Sql

From: <ak_tiredofspam_at_yahoo.com>
Date: 18 Jan 2005 07:49:03 -0800
Message-ID: <1106063343.612382.242300_at_z14g2000cwz.googlegroups.com>

>> I think NULL means a lack of a value .... I think a zero length
string
is a zero length string from twoentirely different things. BTW ... NULL in Oracle does not have single quotes around it. ..<<

I think you aren't getting it

let's see if Oracle can compare two known zero-length strings. One would not expect comparing to NOT NULL values result in UNKNOWN, but strangely enough that's the case

select case when ''='' then 'same string' when ''<>'' then 'different strings'
else 'unknown' end t from dual
T

---

unknown
1 row selected

let's see if Oracle can compare a known zero-length string to another known string

select case when ''='a' then 'same string' when ''<>'a' then 'different strings'
else 'unknown' end t from dual
T

---

unknown
1 row selected

How come? The reason is simple: Oracle implicitly converts '' to null, see for yourself:

select case when '' is null then 'this known string is converted to NULL'
else 'this known string is not converted to NULL' end t from dual T

---

this known string is converted to NULL 1 row selected Received on Tue Jan 18 2005 - 16:49:03 CET