A related comment from the Concepts Manual: "The ANSI/ISO SQL92 (entry-level) standard permits a composite foreign key to contain *any* value in its non-null columns if any other column is null, *even* if those non-null values are *not* found in the referenced key. By using other constraints such as NOT NULL and CHECK constraints, you can alter the treatment of partially null foreign keys from this default treatment."
Note the explicit comment about the value not having to exist in the referenced column if the other column is null. Intended behavior. Anyway, it then goes on to talk about "Match Full", "Match Partial", and "Match None". Best I remember, it's not in the doc's but maybe a Metalink doc that goes on to say that the Match Partial is not supported, at least not without check constraints or not nulls as talked about above. Match Partial: "Partially null composite foreign keys are permitted. Either all components of the foreign key must be null, or the combination of non-null values contained in the foreign key must appear in the corresponding portion of the primary or unique key value of a single row in the referenced table" And as demonstrated earlier, that's not natively enforced. To do so, either define both as NOT NULL, or if optional relation, include a check constraint that enforces both being null, or, both containing values. Now I really loved the DB I worked on with a mandatory exclusive arc. Both had to be defined not null, but no check constraint to enforce it. So sometimes *both* had values (how about that exclusive arc ;-)), or other times neither had a value (how about that mandatory). And then I guess one could use surrogates instead ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:root@;fatcity.com]On Behalf Of > [EMAIL PROTECTED] > Sent: Wednesday, October 30, 2002 12:05 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Data modeling question > > > 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 > > > > > > > [EMAIL PROTECTED] > Sent by: [EMAIL PROTECTED] > 10/30/2002 08:08 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Larry Elkins INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
