Informix 14.10.FC11W1

Assume the following table definition:

create table regional_users (
    employee_num SERIAL,
    region CHAR(3),
    lastname VARCHAR(30),
    firstname VARCHAR(30),
   email VARCHAR(80),
    user_type CHAR(1) DEFAULT 'X' NOT NULL,
);

If we try the following INSERT:

INSERT INTO regional_users

VALUES (0, "SW1", "Flintstone", "Fred", "fred.flintstone@bedrock.org", NULL); 

we get error -391 "Cannot insert a null into column (regional_users.user_type)".  

The manual states "Use the DEFAULT clause in the CREATE TABLE statement to specify the default value for the database server to insert into a column when no explicit value for the column is specified."  I read that as saying that if we do not provide a value, the user_type column would default to "X".  But it looks like since we provided NULL in the VALUES clause, that means that we have provided an explicit value of NULL, which violates the NOT NULL constraint.  Is that what is happening here?

If so, then under normal circumstances, we could change the INSERT to be:

INSERT INTO regional_users
    ( employee_number, region, last_name, first_name, email) 
VALUES (0, "SW1", "Flintstone", "Fred", "fred.flintstone@bedrock.org"); 

HOWEVER, this assumes that you have control over the format of the INSERT statement.  In this case, our application is using the Informix Perform screen program.  There is no INSERT statement in the Perform application.  Perform handles the INSERT/UPDATE/DELETE logic implicitly.  And the user_type is not a field on the Perform screen (it is handled elsewhere within the system), so we cannot simply put change the Perform logic to enforce the NOT NULL.

Given that, how do we get the default value of "X".

Informix 14.10.FC11W1

Assume the following table definition:

create table regional_users (
    employee_num SERIAL,
    region CHAR(3),
    lastname VARCHAR(30),
    firstname VARCHAR(30),
   email VARCHAR(80),
    user_type CHAR(1) DEFAULT 'X' NOT NULL,
);

If we try the following INSERT:

INSERT INTO regional_users

VALUES (0, "SW1", "Flintstone", "Fred", "fred.flintstone@bedrock.org", NULL); 

we get error -391 "Cannot insert a null into column (regional_users.user_type)".  

The manual states "Use the DEFAULT clause in the CREATE TABLE statement to specify the default value for the database server to insert into a column when no explicit value for the column is specified."  I read that as saying that if we do not provide a value, the user_type column would default to "X".  But it looks like since we provided NULL in the VALUES clause, that means that we have provided an explicit value of NULL, which violates the NOT NULL constraint.  Is that what is happening here?

If so, then under normal circumstances, we could change the INSERT to be:

INSERT INTO regional_users
    ( employee_number, region, last_name, first_name, email) 
VALUES (0, "SW1", "Flintstone", "Fred", "fred.flintstone@bedrock.org"); 

HOWEVER, this assumes that you have control over the format of the INSERT statement.  In this case, our application is using the Informix Perform screen program.  There is no INSERT statement in the Perform application.  Perform handles the INSERT/UPDATE/DELETE logic implicitly.  And the user_type is not a field on the Perform screen (it is handled elsewhere within the system), so we cannot simply put change the Perform logic to enforce the NOT NULL.

Given that, how do we get the default value of "X".

Informix 14.10.FC11W1

Assume the following table definition:

create table regional_users (
    employee_num SERIAL,
    region CHAR(3),
    lastname VARCHAR(30),
    firstname VARCHAR(30),
   email VARCHAR(80),
    user_type CHAR(1) DEFAULT 'X' NOT NULL,
);

If we try the following INSERT:

INSERT INTO regional_users

VALUES (0, "SW1", "Flintstone", "Fred", "fred.flintstone@bedrock.org", NULL); 

we get error -391 "Cannot insert a null into column (regional_users.user_type)".  

The manual states "Use the DEFAULT clause in the CREATE TABLE statement to specify the default value for the database server to insert into a column when no explicit value for the column is specified."  I read that as saying that if we do not provide a value, the user_type column would default to "X".  But it looks like since we provided NULL in the VALUES clause, that means that we have provided an explicit value of NULL, which violates the NOT NULL constraint.  Is that what is happening here?

If so, then under normal circumstances, we could change the INSERT to be:

INSERT INTO regional_users
    ( employee_number, region, last_name, first_name, email) 
VALUES (0, "SW1", "Flintstone", "Fred", "fred.flintstone@bedrock.org"); 

HOWEVER, this assumes that you have control over the format of the INSERT statement.  In this case, our application is using the Informix Perform screen program.  There is no INSERT statement in the Perform application.  Perform handles the INSERT/UPDATE/DELETE logic implicitly.  And the user_type is not a field on the Perform screen (it is handled elsewhere within the system), so we cannot simply put change the Perform logic to enforce the NOT NULL.

Given that, how do we get the default value of "X".