Default value is not reflecting in the table for a column

General

Default value is not reflecting in the table for a column

hi,

I have a SET table where one of the column is defined as

Sub_Cat VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'FI',

Ideally it should take default value 'FI' whenever a NULL is suplied. But we noticed there are certain cases where 'FI' is not getting reflecting eventhough NULL values are supplied. We are not sure in which load this had happened, but when I manually tried to insert into the tables now, it is working properly and taking default 'FI' whenever we supply NULLS. So can anyone helpme in understanding why it is not taking default 'FI' all the times whenever we supply NULLs. It is working differntly at differnt times.

Thanks in advance.

1 REPLY
Senior Apprentice

Re: Default value is not reflecting in the table for a column

A default value is only set where there's no value supplied, and "no value" is not the same as NULL.

For single row inserts:

insert into table(a,b,NULL) will keep the NULL.

insert into table(a,b,) will set the default.

This is based on Standard SQL and should be the same for all RDBMSes.

For Insert/Selects you should use the DEFAULT function:

insert into target select a,b, DEFAULT(c) from source

Dieter