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

Hi Alan,

ORACLE defaults to allowing NULL-values for columns. If you want to to change this you must define your table as:

CREATE TABLE tableb (aa1 number NOT NULL, aa2 number NOT NULL, aa3 number NOT NULL);

-- 
Regards

M.Gresz    :-)