Re: Foreign key in Oracle Sql

Gene Wirchenko wrote:

> On Tue, 18 Jan 2005 15:07:46 -0800, DA Morgan
> <damorgan_at_x.washington.edu> wrote:
>
>

>>David Cressey wrote:
>>
>>><ak_tiredofspam_at_yahoo.com> wrote in message
>>>news:1106063343.612382.242300_at_z14g2000cwz.googlegroups.com...

>>>I think Oracle isn't getting it.  The zero length string is not the absence
>>>of data,  just as the number zero isn't the absence of a number.  Oracle's
>>>support for NULLS is limited,  and this is one place where they screwed up.
>>>They admit it in their own documentation.
>>>
>>>There are other DBMS products that get this one right.
>>
>>Given that those other products didn't exist when Oracle was created.
>>And given that those that have worked with Oracle for almost 20 years
>>have a large volume of working code they don't want broken. And given
>>that it really doesn't matter ... what's your point other than whining?
>>
>>That Oracle should gut its product and make it just another flavour of
>>Microsoft vanilla?

Yes. Because that behaviour is expected by every bit of code in the database. The code written by developers is not the issue. It is all of the code from which the kernel is built, all of the code in all of the built-in packages and libraries, etc. You are talking about throwing millions of lines of code into doubt and a full testing cycle on features and code that have been stable for a very long time.

And if Oracle did it would they sell one more database license? Would there be any revenue generated in exchange for the massive expense? I think a reasonable person would conclude that it would be a very large investment against a zero prospect of a return on that investment. Then look at it from the standpoint of all of the major application vendors such as SAP, PeopleSoft, Siebel, Baan, etc. whose code sits on top of Oracle? How much of their code would need to be rewriten and retested? How about that in SQL*Navigator? TOAD? etc. Then then there is all of the code written by banks, government organizations, corporations, non-profits, etc. That little behavior of NULL is a very very big thing not like, for example adding ANSI joins to the ISO syntax.

With joins Oracle can add a second syntax and resolve them to perform the same function. But with the difference in the behavior NULL it is not one that lends itself to if you see '' treat it one way sometimes and another way at other times. Maybe in new code written by end-users but not in existing apps, not in the data dictionary, and not in the database engine.

HTH explain it as I see it from where I stand.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)