Inserting a null into a not null column defined as NOT NULL DEFAULT=0

Database

Inserting a null into a not null column defined as NOT NULL DEFAULT=0

We are currently experiencing either a limitation or a bug attempting to insert a null value into a field defined as not null with a default=0.  I would think that if a null were encountered, a 0 would be inserted, but instead we are getting the following error message:

INSERT FAILED.  [3811] Column 'KEY2' is NOT NULL.  Give the column a value.

Does anyone know if this is a limitation or a bug?  It is very easy to reproduce in SQLA.

Thanks,

Joe

2 REPLIES
N/A

Re: Inserting a null into a not null column defined as NOT NULL DEFAULT=0

Default value will be picked when no value is supplied.However if you supply NULL ,NULL value would be picked.

It is neither limitation nor bug , it is an expected functionality.

Re: Inserting a null into a not null column defined as NOT NULL DEFAULT=0

Thank you very much for your response Jigar!  I was under the assumption that null would be treated as a 'no value' candidate for the default replacement.  I guess it makes sense that the 'unknown value' of a null is a value.  :)  If I replace the null with '' (tic tic) empty space, then the default value is accepted.  We were trying to take advantage of data services pushdown with an insert/select whose key fields are populated with an outer join lookup.  If the key is not found, then a null is returned.  ETL put the default constraint in order to default the missing key look ups to 0 if not found.  I think they can replace the null with an empty space and then we are good. 

Just out of curiosity, does anyone know if Oracle or SQL handle default constraints the same way when trying to insert a null?

Thanks again,

Joe