RE: Referential Constraint
Yes, thanks.
My "referencing" table had two foreign keys, one numeric and
the other CHAR. I've, since my original post, found that the
numeric column accepts a null string as a Null value,
whereas the CHAR column does require the keyword NULL.
Alanoly J. Andrews
> -----Original Message-----
> From: Dragi Raos [SMTP:draos@4mate.hr]
> Sent: Tuesday, January 05, 1999 1:56 PM
> To: informix-list@iiug.org
> Subject: Re: Referential Constraint
>
> Alanoly Andrews wrote:
> >
> > Hi,
> >
> > I've come across a problem in the Informix implementation of
> > "referential integrity constraint". According to Informix's SQL
> > manual, foreign key columns in a referencing table are allowed
> > to have null (and duplicate) values (whereas "referenced" columns
> > should be non-null and unique). But this principle seems to have
> > been implemented only in the "load" command and not in the
> > "insert" command.
> >
> > That is, the "load" statement can insert into null values into a
> > referencing column; and the "insert" statement cannot (with
> > error number 691: "missing key in referenced table for referential
> > constraint").
> >
> > Is this a known bug in the implementation of the "insert" command?
> >
> > Informix Online Dynamic Server 7.22 UC2 on AIX 4.1.4
> > Also in IDS 7.30 UC3 on AIX 4.3.1
> >
> > Thanks.
> >
> > Alanoly J. Andrews
>
> If you mean something like this:
>
> >>>>>>
> create table a (
> col1 integer not null,
> col2 char(20),
> primary key (col1)
> );>
> create table b (
> cola integer not null,
> colb integer,
> colc char(20),
> primary key (cola),
> foreign key (colb) references a(col1)
> );>
> insert into a values (1,"111");
> insert into a values (2,"111");
> insert into b values (1, 1, "aaaa");
> insert into b values (2, null, "bbb");>
> <<<<<<<<<<<
>
> it works with all Ifmx version and OS combination I tried. Are you
> sure
> you put explicit NULL in your insert (like in the last of my inserts),
> not just empty string? They are not the same.
>
> Cheers!
>
> Dragi "Bonzi" Raos
> 4-MATE Information Enginnering
> Zagreb, Croatia