Re: Is this supposed to work like this?

From: Alan Caldera <acaldera_at_airmail.net>
Date: 1997/10/30
Message-ID: <8FACD3CA9C74CD55.A0009B7BAF34AC56.AB8E5C6AF2071670@library-proxy.airnews.net>#1/1

On Wed, 29 Oct 1997 11:51:57 -0500, "Joseph D. Sumalbag" <joseph_sumalbag_at_bose.com> wrote:

>Alan Caldera wrote:

>>
>> I came across a condition today that did'nt make sense to me.
>> Consider the following problem:
>>
>> CREATE TABLE tablea (a1 number, a2 number, a3 number);
>> ALTER TABLE tablea ADD PRIMARY KEY (a1, a2);
>> INSERT INTO tablea VALUES(1,2,3);
>> INSERT INTO tablea VALUES(4,5,6);
>> COMMIT;
>>
>> CREATE TABLE tableb (aa1 number, aa2 number, aa3 number);
>> ALTER TABLE tableb ADD PRIMARY KEY (aa1);
>> ALTER TABLE tableb ADD FOREIGN KEY (aa2, aa3);

Meant to say REFERENCES tablea.

>> INSERT INTO tableb VALUES(1,1,null);
>> COMMIT;
>>
>> Why do I not get a FK violation on the insert into table B? If I put
>> in an obviously invalid combination in aa2 and aa3 then I get the
>> appropriate parent key not found message.
>>
>> Any ideas?
>>

Well, after thinking more about it and consulting with various sources, I have answered my own question. BTW, it is legal to say REFERENCES tablea without naming the columns explicitly.

It turns out that Oracle permits partially null foreign keys in accordance with ISO-ANSI SQL 92. To truly enforce the condition you desire (ie having both parts of the key), one must either employ a NOT NULL or CHECK constraint. I came to this after further realizing that if I neglected to properly enforce it and specified ON DELETE CASCADE in the foreign key definition, that I would end up with orphan records. NOT GOOD.

In short, to ensure that referential integrity in enforced across multiple columns and still allowing the fields to be 'optional':

ALTER TABLE tableb ADD CONSTRAINT ck_fk_intact CHECK ((aa2 is not null and aa3 is not null) or (aa2 is null and aa3 is null));

Adding this constraint produces what I would consider to be proper behaviour. I think I will add this to my list of interview questions to harass candidates with. ;-).

Thanks to all that responded.

Alan Caldera   Received on Thu Oct 30 1997 - 00:00:00 CST