quick FK question

From: Sweetser, Joe <JSweetser_at_icat.com>
Date: Tue, 15 Dec 2009 14:22:25 -0700
Message-ID: <89FD2C8D7D551D4598EF20C7E42FEB450521D2D3_at_earthquake.ICAT.COM>

I'm having trouble understanding FK's and NULL's. It appears I can insert a null value to a foreign key which is not what I would expect. Is the "solution" to put NOT NULL on the column definition?

Thanks,
-joe

RH5
10.2.0.4

(Hope this formats OK)

SQL> create table test2
  2 (t2_pk number,
  3 lastname varchar2(50),
  4 constraint pk_t2 primary key (t2_pk) validate)   5 /

Table created.

SQL> create table test1
  2 (t1_pk number,
  3 name varchar2(50),
  4 test2_fk number,
  5 constraint fk_test2 foreign key (test2_fk)   6 references test2 (t2_pk) validate,   7 constraint pk_t1 primary key (t1_pk) validate)   8 /

Table created.

SQL> insert into test2 values (1, 'Smith');

1 row created.

SQL> insert into test1 values (1, 'Joe', 1);

1 row created.

SQL> insert into test1 values (2, 'Bill', 2); insert into test1 values (2, 'Bill', 2)
*
ERROR at line 1:
ORA-02291: integrity constraint (INSPPROD.FK_TEST2) violated - parent key not
found

SQL> insert into test1 values (2, 'Bill', NULL);

1 row created.

SQL> commit;

Commit complete.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 15 2009 - 15:22:25 CST