04 December 2013

Invisible Not Null Column

Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he discussed the possibility to have invisible columns in a table, it triggered the question: What happens when a NOT NULL column is made invisible? To see what would happen, I set up a small test case on my Oracle 12c database.
SQL> select banner
  2    from v$version
  3  /

Oracle Database 12c Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - Production
First create a table, two columns - both NOT NULL, and some data.
SQL> create table t
  2  (id   number not null
  3  ,text varchar2(10) not null
  4  )
  5  /

Table created.

SQL> insert into t values (1, 'Hello')
  2  /

1 row created.

SQL> insert into t values (2, 'World')
  2  /

1 row created.

SQL> commit;

Commit complete.
Next to change the TEXT column to be invisible.
SQL> alter table t modify (text invisible)
  2  /

Table altered.

SQL> desc t
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------

SQL> select * from t
  2  /

The data that we entered is still there, or at least the ID values are still there.
So the column is now invisible, what if we try to add new data into it?
SQL> insert into t values (3)
  2  /
insert into t values (3)
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T"."TEXT")

Even though you can't see the column, the NOT NULL constraint is still enforced.
When you name the column explicitly, you can still do DML on it.
SQL> update t 
  2  set text = upper(text)
  3  /

2 rows updated.
But not like this
SQL> insert into t values (3, 'Goodbye');
insert into t values (3, 'Goodbye')
ERROR at line 1:
ORA-00913: too many values
This following is still valid, and again shows that naming the columns in an INSERT statement is a good practice.
SQL> insert into t (id, text) values (3, 'Goodbye');

1 row created.