Re: Foreign key not to enforce when null value

"Nits" <nitinvraj_at_gmail.com> wrote in message news:1143451440.456836.193320_at_j33g2000cwa.googlegroups.com...
: Hi all,
:
: I want to know is there any way that when I insert null or zero as id,
: it will not check the foreign key constraint, and will check only for
: non-zero and non-null values.
:
: For example when I insert saleamanID in customer table as null or zero
: it still checks in the salesman master for parent key and gives error
: parent key not found.
:
: Thanks
:
: NITIN.
:

null is allowed (it is an unknown or missing value and is not checked), zero is not (it is a value and is checked)

you should use nulls, not 0 or other placeholders, for missing or unknown values

++ mcs

SQL> create table pktbl (id number primary key);

Table created.

SQL> insert into pktbl values(1);

1 row created.

SQL> create table fktbl (id number primary key, pkid references pktbl);

Table created.

SQL> insert into fktbl values(1,1);

1 row created.

SQL> insert into fktbl values(2,2);
insert into fktbl values(2,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (OEX.SYS_C004284) violated - parent key not found

SQL> insert into fktbl values(2,0)
  2 ;
insert into fktbl values(2,0)
*
ERROR at line 1:
ORA-02291: integrity constraint (OEX.SYS_C004284) violated - parent key not found

SQL> insert into fktbl values(2,null);

1 row created. Received on Mon Mar 27 2006 - 04:29:30 CST