CODEWITHSUNDEEP
sqldb2
Sabir Khan
Sabir Khan

Reputation: 10142

Default Value for NOT NULL VARCHAR in DB2

I have a RDBMS table in DB2 where a column is defined as "ARMTST" VARCHAR(10) NOT NULL DEFAULT

I was checking data on table and see rows with no value in this column ( Via Toad Viewer) but when I run this query , SELECT COUNT ( * ) FROM ACTIVITY WHERE ARMTST IS NULL; I get result as zero rows.

Attached is screen shot for SELECT ARMTST FROM ACTIVITY ; which shows empty column values for certain rows.

Are these columns not empty even though shown so in UI? Default value is not specified in CREATE TABLE script.

I don't think that code will be able to insert empty values.

DB Data

Upvotes: 0

Views: 2688

Answers (1)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

An empty string is not null (well, except for on Oracle, but people hate them for that). Often in databases, null is used to represent "we don't know (yet)", while empty is "not present".

Consider middle names. Many people in America (and other countries) have middle names (not your given name, not your family name).
If you (casually) ask me for my name and I respond with only a first and last name (likely, in my case, or you might only get my first name!), what do you know about my middle name? Nothing. You don't know if I have one. This is null - you don't know if I have one, and if so, what it is. But if you ask me "officially" (like for legal reasons), I'm obligated to use my full name - if I have one (or more), I have to include my middle name. So there's two outcomes here:

  1. I have no middle name. That result is blank - we know the answer, and it was nothing.
  2. I have a middle name. The result is whatever my name is.

Or consider signing up for a website. Before signing up, you are not in their system. Your entire record is null - it doesn't exist. After signing up, though, you get a 0 post count.

So now you should have enough information for your question.
Obviously, because the column was defined as NOT NULL, you can't put a null there. So the system has to default to something else. Since including any data (including a space character - yes, there is one) would make a poor default, the system chooses an empty string. And since the empty string makes an acceptable default, it will also be an acceptable data value to insert.

Upvotes: 1

Related Questions