Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Foreign Key Problem, better stated

Foreign Key Problem, better stated

From: Brian Lavender <blavender_at_spk.usace.army.mil>
Date: Tue, 08 Dec 1998 00:45:55 GMT
Message-ID: <74hss3$jtp$1@nnrp1.dejanews.com>


I have a problem maintaining referential integrity using a foreign key specification. Why is it it won't maintain the integrity when null values are in a lookup table? My situation is detailed as follows.

I have a table containing accounts which consists of a two column composite key. Here is a sample table, with the two columns labeled old_num, new_num. You will note that some accounts have a null value for either of the columns. Some accounts have a value in both columns. This is the result of accounting changes from an old system to a new system. Some old accounts were empty before conversion occured and new ones are created after the conversion

ACCOUNTS
old_acct new_acct
AA1234 000123
BB4567

           009876
           004234
AA6543     004321

I have a detail table which refers to this this table's composite key as a foreign key. Here is the detail table

Payments
ContractNo ProjectNo old_acct new_acct amount

222222      AAAA1111  AA1234   000123   400
222222      AAAA2222  BB4567            200

Creating the Payments table I used the foreign key creation foreign key (old_acct, new_acct) references ACCOUNTS(old_acct, new_acct)

and I indeed verified these references were created by viewing the indexes in the system's user_constraints table. However, I noticed if I insert the following values into the detail table

ContractNo ProjectNo old_acct old_acct new_acct amount

222222      AAAA1111  BB4444                     400

it does not give me a violation of referential integrity. I would think it would, but I found that it only complained if I violated referential integrity if I specified both fields. I was thinking that rather than leaving null values where there is not old_acct or new_acct value that I should place a "none" value. I could see this as a fix, but I am wondering, why I can't leave it null and have it work?

brian

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Dec 07 1998 - 18:45:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US