Oracle-L: RE: Data modeling question

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 30 Oct 2002 08:49:10 -0800
Message-ID: <F001.004F7925.20021030084910@fatcity.com>

Rick,

This goes back to relational theory.

A NULL value is part of the "Empty Set". Theory says that the "Empty Set" contains nothing. Thus, you cannot test for the existence of nothing, only for the existance of something.

Thus, Referential Integrity does not apply to this set of values (the NULL set).

This is why, in a where clause, you cannot compare a column containing a NULL value with another column containing a value - the comparison is invalid, and the where clause will not select the record (like saying "AND null_column <> non_null_column").

This is also why an index will not contain an entry for a record where the indexed column is NULL - and this would really cause RI to slow down, because a full-table-scan would be required to find these records.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, October 30, 2002 11:09 AM To: Multiple recipients of list ORACLE-L

I was reading one of the papers on the quest website about data modeling. An example was presented and I did not believe the results so I tried it out. Of course the writer ( Phd) was correct.

Does anyone know why does not enforce RI constraint if any of the foreign key colums are NULL. I took it literally that if I insert any record in table_2 that a corresponding record must be in the parent(table_1). Apparently this is not so. Someone please explain.

Thanks
Rick

drop table table_2;
drop table table_1;

create table table_1 (
  a number not null,
  b number not null,
  c number,
constraint table_1_pk primary key (a,b));

create table table_2 (
a number,
b number,
d number not null,
e number not null,
f number,
constraint pk_table_2 primary key (d,e), constraint fk_table_2_reference_table_1 foreign key (a,b) references table_1 (a,b))
/

insert into table_2 values(1,NULL,3,4,5);

1 row processed.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------