Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Foreign Key Problem, better stated
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