Re: Insert Problem

In <2078@das13.snide.com> dave@das13.snide.com (Dave Snyder) writes: >In article <1993Sep6.125832.27915@lmpsbbs.comm.mot.com> ramesh@miel_020.miel.mot.com (Ramesh Pathak) writes: >} >} I have a cloumn in a table which is defined to be unique but Nulls >} allowed. >} >} And when I try to insert a null value in the same columns, informix online >} returns me with an error no. 239! >} >} What could be the problem? Can I not have a filed unique but allowing >} nulls? >} >A -239 error is a duplicate record error. Apparently you already have a >record out there with a NULL in the index column. NULL is a value and you >can only store one of them. This is actually a shortcoming of Informix' indexing algorithm, although a UNIQUE constraint should (in theory) allow multiple NULLS (as NULL is not a value, Dave, it is an unknown) it is currently implemented in Informix as a Unique Index, and Informix *does* store it as a value in both the table and the index (unlike, for instance, Oracle) and an Informix Unique Index will not allow multiple NULLs. graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.