Re: Problems with DEFAULT column

>From: Robert Hale <robert_hale@nortel-nsm.com> >Date: Thu, 10 Jul 1997 17:11:49 -0700 >X-Informix-List-Id: <news.40332> > >I'm trying to ensure that a field in a table which is restricted to >being either 0 or 1 is never left with a NULL value. However, the >default assignment on the appropriate attribute. The obvious (and critical) first step is to make sure the column is declared with the NOT NULL constraint. >Even if I have specified a default value for the attribute, other >applications, such as forms and some generated ESQL insert statements >always seem to ensure that the field has a NULL value. The DEFAULT specified only applies if the INSERT statement does not provide a value for the column. Let's assume that your table is table X below, and the column your currently concerned about it B. CREATE TABLE X ( A INTEGER NOT NULL, B INTEGER DEFAULT 0, C INTEGER NOT NULL ); If you do: INSERT INTO X VALUES(12, NULL, 6); you've specified a value (NULL) for the B column, so the default has no chance to take effect. To have it take effect, you'd have to write: INSERT INTO X(A, C) VALUES(12, 6); Of course, if your column B has the NOT NULL constraint on it, then the first insert will be rejected because NULL is not allowed in column B. Any applications which try to insert a NULL in column B will of course fail after you apply the NOT NULL constraint, but that's OK -- you do not want them to insert a NULL in there. >For example, I have altered the table with the following command: >alter table historic_data>modify fault_view integer default 0 What you should use is: ALTER TABLE Historic_Data MODIFY (Fault_View INTEGER DEFAULT 0 NOT NULL CHECK (Fault_View IN (0, 1))); The chances are this will fail because there already is null data in the table, so we need to fix that: BEGIN WORK; UPDATE Historic_Data SET Fault_View = 0 WHERE Fault_View IS NULL OR Fault_View != 1; ALTER TABLE Historic_Data MODIFY (Fault_View INTEGER DEFAULT 0 NOT NULL CHECK (Fault_View IN (0, 1))); COMMIT WORK; >If I attempt to datafill the table using the Informix LOAD command and >leave the fault_view field as NULL in the data file, then each record >created during the load will have a NULL value in fault_view. That is a valid observation, but it is also the correct behaviour. Unless you omit the Fault_View column data from the load file, and unless you specify the columns to be loaded carefully omitting Fault_View from the list, the INSERT statement generated for the load operation will stuff a value into the Fault_View column, thus preventing the DEFAULT from taking effect. LOAD FROM "datafile" INSERT INTO Historic_Data (Column1, ... ColumnN); You may need to use DBLOAD to skip the empty Fault_View column if your data files have already been created and cannot be recreated without the empty Fault_View column. >The desired behaviour would be to have fault_view set to 0 even if the >LOAD command is used to data fill the table. Well, you either have to use the correct load command, or the correct data file where you have fixed the file so that it contains 0's instead of nulls in the Fault_View column. >Does anyone know the syntax for a trigger that would reset the value >of the fault_view field BEFORE it was inserted into the table? There is no way to alter the inserted value in a column where the data value is specified by the INSERT statement -- eg in the load statement. If you specify (implicitly or explicitly) the Fault_View column in the INSERT statement for the LOAD, then the value in the data corresponding to the Fault_View will be loaded. >Is a trigger the answer or is there a better mechanism? See the various alternatives outlined above. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: I decline to respond to messages with anti-spam in the return path.