Re: Is this supposed to work like this?

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);
> 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?
>
> Thanks.
>
> Alan Caldera
> acaldera_at_airmail.REMOVETHISSPAMBLOCKER.net

The statement <<ALTER TABLE tableb ADD FOREIGN KEY (aa2, aa3);>> is not valid. It doesn't say what primary or unique key it should match. So the foreign key is not created.

If this was just a watered-down example, please be a little more specific as to how you create the foreign key, and we can try to help.

--
Joe Rogers
Intelligent Information Systems, Inc.
joer_at_renewal-iis.com