Referential Constraint ~ Answered: amber (solid confidence) — First reply (nulls shouldn't be in FK columns) is confusing/wrong per SQL semantics and gets walked back by its own author; Dragi Raos then gives a concrete worked example showing null-in-FK insert works fine and suggests the real cause is an empty string being inserted instead of an explicit NULL, a classic Informix CHAR gotcha, but the original asker never confirms.

Advisory only.

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

What part is a bug? Nulls should not be present in referential columns. By definition, the data must be present in the controlling tables primary index which must be unique and, unless RI is a hobby, won't contain nulls.

In article <76bd9e$lvv$1@news.xmission.com>, Alanoly Andrews <AlanolyA@planmatics.com> 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"). Just want to add to answer of m00n321@aol.com: Check the value inserted by the load statement. It's probably not null, whereas you inserting null in insert statement. > > 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 > > -- Vardan Aroustamian -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Hi, Sorry for my previous posting. Actually yes, you should be able to insert null into a referencing column. I don't know what's going wrong in your case. May be it's a bug. > In article <76bd9e$lvv$1@news.xmission.com>, > Alanoly Andrews <AlanolyA@planmatics.com> 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"). > > Just want to add to answer of m00n321@aol.com: > > Check the value inserted by the load statement. > It's probably not null, whereas you inserting null in insert statement. This can happen with load if your referencing/ed column is char. But if you really inserting null in both cases, then behaviour of load and insert should be the same. > > > > > 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 > > > > > > > -- > Vardan Aroustamian > > Happy New Year -- Vardan Aroustamian -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

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