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.

Labels: database, null, oracle

  ¶ 6/24/2009 05:24:00 PM