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.