NULL

A NULL Observation, III

Part I here.

Part II here.

OK, we have a winner. Coskan Gundogar suggested in the comments, that using the MODIFY clause of the ALTER TABLE statement should work. Let's see:

desc t_null

Name                    Null?    Type
----------------------- -------- ----------------
COL1                             VARCHAR2(30)

CJUSTICE@TESTING>ALTER TABLE T_NULL DROP CONSTRAINT nn_col1_tn;

Table altered.

CJUSTICE@TESTING>ALTER TABLE t_null MODIFY ( col1 CONSTRAINT nn_col1_tn NOT NULL );

Table altered.

CJUSTICE@TESTING>@DESC T_NULL
Name                    Null?    Type
----------------------- -------- ----------------
COL1                    NOT NULL VARCHAR2(30)

Voila!

Much better. The question still remains as to what's going on in the background. Until now, I had never thought that white space mattered in anything Oracle. Very strange.

Maybe I can get Miladin to dig through the internals and see what's really going on.

A NULL Observation, II

Shoot me, I couldn't let this go. Plus, I needed a reason to test drive my new sandbox.

The part about the space had me a little perplexed:

CJUSTICE@TESTING>SELECT
  2    table_name,
  3    constraint_name,
  4    constraint_type,
  5    search_condition,
  6    status
  7  FROM user_constraints
  8  ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  "COL1" IS NOT NULL  ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Here's the DDL that created that constraint:

ALTER TABLE t_not_null_check DROP CONSTRAINT nn_col1_tnnc;

ALTER TABLE t_not_null_check
  ADD CONSTRAINT nn_col1_tnnc
  CHECK ( "COL1" IS NOT NULL );

Note the white space there. I like putting a space after a parenthesis as I believe it makes it slightly easier to read.

With that in mind, watch this:

ALTER TABLE t_not_null_check DROP CONSTRAINT nn_col1_tnnc;

ALTER TABLE t_not_null_check
  ADD CONSTRAINT nn_col1_tnnc
  CHECK ("COL1" IS NOT NULL);

Note that I removed the space between the "(" and the """. Here's what it looks like:

SELECT 
  table_name, 
  constraint_name, 
  constraint_type, 
  search_condition
FROM user_constraints
ORDER BY table_name;

TABLE_NAME       CONSTRAINT_N C SEARCH_CONDITION
---------------- ------------ - ------------------------------
T_NOT_NULL       NN_COL1_TNN  C "COL1" IS NOT NULL
T_NOT_NULL_CHECK NN_COL1_TNNC C "COL1" IS NOT NULL
T_NULL           NN_COL1_TN   C "COL1" IS NOT NULL

CJUSTICE@TESTING>desc t_not_null_check
 Name    Null?   Type
 ----------------------- -------- ----------------
 COL1      VARCHAR2(30)

The leading space is gone now. Null? still shows up as NULL. I would assume that most GUI apps get their data from USER/ALL/DBA_TAB_COLUMNS, so let's take a look:

SELECT 
  table_name, 
  column_name, 
  nullable
FROM user_tab_columns
ORDER BY table_name;

TABLE_NAME       COLUMN_NAME                    N
---------------- ------------------------------ -
T_NOT_NULL       COL1                           N
T_NOT_NULL_CHECK COL1                           Y
T_NULL           COL1                           Y

How about USER_TAB_COLS?

SELECT 
  table_name, 
  column_name, 
  nullable
FROM user_tab_cols
ORDER BY table_name;

TABLE_NAME       COLUMN_NAME                    N
---------------- ------------------------------ -
T_NOT_NULL       COL1                           N
T_NOT_NULL_CHECK COL1                           Y
T_NULL           COL1                           Y

Nothing to see there.

So, there is a difference between defining a NOT NULL constraint using either the NOT NULL or CHECK syntax. In USER_CONSTRAINTS, there are 4 distinct constraint types:
1.  P = PRIMARY
2.  U = UNIQUE
3.  R = REFERENCE or FOREIGN KEY
4.  C = CHECK

What's the lesson here? Well, if doing analysis, you can't just depend on using the DESCRIBE command from SQL*PLus to see what is required and what is not. Of course you can't depend on that for everything. Interesting "problem" none-the-less.

Update
Part III (the final solution) is here.

A NULL Observation

As I've been doing a lot of analysis lately, I've found a slightly annoying "problem."

I typically use JDev or SQLDev to see details on a table, more than just a DESCRIBE from SQL*Plus can give me anyway.

This "problem" relates to how NULL columns are displayed, both via DESCRIBE and the previously mentioned tools.

First up, my favorite, the table definition with inline constraints.

CREATE TABLE t_not_null
(
  col1 VARCHAR2(30)
    CONSTRAINT nn_col1_tnn NOT NULL
);

Let's see how it looks in a SQL Worksheet (Columns):
Constraints:
SQL*Plus (DESCRIBE):

CJUSTICE@TESTING>desc t_not_null
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                    NOT NULL VARCHAR2(30)

Now I'll create a different table, this time instead of using NOT NULL, I'll use the CHECK syntax.

CREATE TABLE t_not_null_check
(
  col1 VARCHAR2(30)
    CONSTRAINT nn_col1_tnnc CHECK ( col1 IS NOT NULL )
);

Columns:
Nullable = Yes? Hmmm...

Constraints:

DESCRIBE

CJUSTICE@TESTING>desc t_not_null_check
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

Interesting, it doesn't show up in the "Null?" column like it did with the NOT NULL syntax used above.

Now I'll create a table with no inline constraint defined.

CREATE TABLE t_null
(
  col1 VARCHAR2(30)
);

I know (famous last words) I can't use the NOT NULL syntax in an out-of-line constraint:

ALTER TABLE t_null
  ADD CONSTRAINT nn_col1_tn NOT NULL;
  ADD CONSTRAINT nn_col1_tn NOT NULL
                            *
ERROR at line 2:
ORA-00904: : invalid identifier

So I use the CHECK syntax:

ALTER TABLE t_null
  ADD CONSTRAINT nn_col1_tn
  CHECK ( col1 IS NOT NULL );

Table altered.

Columns:
Constraints:
DESCRIBE

CJUSTICE@TESTING>desc t_null
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

Very odd.

Let's look at the dictionary:

SELECT
  table_name,
  constraint_name,
  constraint_type,
  search_condition,
  status
FROM user_constraints
ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  col1 IS NOT NULL    ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Interesting, I wonder if the fact that it's not UPPERcased and in quotes?

CJUSTICE@TESTING>DROP TABLE t_not_null_check;

Table dropped.

Elapsed: 00:00:00.04
CJUSTICE@TESTING>CREATE TABLE t_not_null_check
  2  (
  3    col1 VARCHAR2(30)
  4      CONSTRAINT nn_col1_tnnc CHECK ( "COL1" IS NOT NULL )
  5  );

Table created.

DESCRIBE

CJUSTICE@TESTING>DESC T_NOT_NULL_CHECK
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

OK, Null? is still...NULL.

CJUSTICE@TESTING>SELECT
  2    table_name,
  3    constraint_name,
  4    constraint_type,
  5    search_condition,
  6    status
  7  FROM user_constraints
  8  ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  "COL1" IS NOT NULL  ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Why is there that extra space in front of "COL1" IS NOT NULL?

I ended my investigation there as it seems to be a waste of time. I just found it interesting that there was a difference in how you defined a NOT NULL constraint and whether or not it would show up in the DESCRIBE command. Anyone out there notice this before?

Read Part II here.

Part III (the final solution) is here.