Re: Foreign key in Oracle Sql

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Wed, 19 Jan 2005 14:06:51 -0800
Message-ID: <41eed9ef$1_1_at_127.0.0.1>

Tony Andrews wrote:

> David Cressey wrote:
>

>>>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?
>

>>My point is not whining.  In fact, my general opinion of Oracle is a
>>favorable one.
>>But if they get something wrong, it deserves to be called wrong.

>
> Especially
>

>>in the context of this thread, where somebody is using Oracle

>
> behavior to
>

>>prove the point to somebody else.

>
>
> I'm afraid you are flogging a dead horse. Some people's love of Oracle
> is so blind that they cannot admit it has any faults whatsoever: Mr
> Morgan may be one of those people. (OTOH, some people's hatred of
> Oracle is so blind that they cannot admit it has any virtues
> whatsoever: Mr Celko may be one of those people ) ;-)

Actually I criticize Oracle a lot. Perhaps more than most other people. But then familiarity often leads to criticism. The difference is that I try to criticize those things that matter and those things that can and possibly will be changed for the better.

> Now I too love Oracle, but (being perfect ;-) can also see that it does
> get one or two things wrong, and that this is one of them. I can also
> sympathise with Oracle that it got it wrong before there was a standard
> to follow, and that it would be a nightmare to try to correct it now
> (though perhaps the thought of doing so is behind the warning that
> VARCHAR may be different from VARCHAR2 in the future).

And if you haven't worked with 10g yet you will discover an interesting new behaviour:

CREATE TABLE t (
testcol VARCHAR(20));

It converts the data type on the fly to VARCHAR2.

But my favorite is still:

CREATE TABLE tnorm (
somecol VARCHAR2(20));

CREATE TABLE tbyte (
somecol VARCHAR2(20 BYTE));

CREATE TABLE tchar (
somecol VARCHAR2(20 CHAR));

desc tnorm
desc tbyte
desc tchar

> "It doesn't really matter" that Oracle treats '' and NULL as being the
> same, but:
> - the inconsistency is unsatisfying (LENGTH('') returns NULL but
> 'abc'||'' returns 'abc')
> - having to write NVL(LENGTH(string),0) all the time is a PITA
> - it causes developers who move from other DBMSs to Oracle to create
> code with bugs like "where middle_name = ''"

Good point. I must concede that in this case it makes sense. But in almost all others it does not. There are plenty of good reasons to never have columns that allow NULL. Middle name is not one for which that choice can be made.

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

Received on Wed Jan 19 2005 - 23:06:51 CET