quick FK question

  • From: "Sweetser, Joe" <JSweetser@xxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 15 Dec 2009 14:22:25 -0700

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?



(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

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

1 row created.

SQL> commit;

Commit complete.


Other related posts: