Oracle-L: Re: Data modeling question

From: <Jared.Still_at_radisys.com>
Date: Wed, 30 Oct 2002 10:05:12 -0800
Message-ID: <F001.004F7B3F.20021030100512@fatcity.com>

Rick,

This doesn't actually have anything to do with data modeling, but rather, database design.

The FK can't be enforced on a multipart key if one of the columns is null, obviously. At least, I *think* it's obvious.

What happen when you try to update column b in table 2 with a valid value?

with and invalid value?

There's a good case here for using surrogate keys, as always.

That way, invalid values cannot be put in an FK column, since there is only one column needed to reference the foreign PK.

Jared

Rick_Cale_at_teamhealth.com
Sent by: root_at_fatcity.com
10/30/2002 08:08 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Data modeling question

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:
INET: Jared.Still_at_radisys.com

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