Skip to Main Content
  • Questions

  • Nullable Property When Copying a Table Using CTAS

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mike.

Asked: August 02, 2016 - 9:57 pm UTC

Last updated: August 04, 2016 - 12:46 pm UTC

Version: 11.2.0.3.0

Viewed 1000+ times

You Asked

Hello,

In the example below, I'm using a CTAS statement with where 1=2 to copy the structure of a table. Four of the columns that are defined as NOT NULL on the source table retain that property in the new table. However, one column (patient_id) becomes nullable. Can you tell me what would cause this behavior?

SQL> desc source_table;
Name                    Type         Nullable Default Comments 
----------------------- ------------ -------- ------- -------- 
PATIENT_ID              NUMBER                                 
RECORD_ID               NUMBER                                 
CLIN_CODE_POSITION      NUMBER                                 
CLIN_CODE_TYPE          VARCHAR2(20)                           
CLIN_CODE               VARCHAR2(36)                           
CLIN_CODE_MODIFIER_TYPE VARCHAR2(13) Y                         
CLIN_CODE_MODIFIER      VARCHAR2(13) Y 


SQL> create table test_copy
  2  nologging
  3  PARTITION BY HASH (PATIENT_ID)
  4  partitions 128
  5  as
  6  select * from source_table s where 1=2;
 
Table created


SQL> desc test_copy;
Name                    Type         Nullable Default Comments 
----------------------- ------------ -------- ------- -------- 
PATIENT_ID              NUMBER       Y                         
RECORD_ID               NUMBER                                 
CLIN_CODE_POSITION      NUMBER                                 
CLIN_CODE_TYPE          VARCHAR2(20)                           
CLIN_CODE               VARCHAR2(36)                           
CLIN_CODE_MODIFIER_TYPE VARCHAR2(13) Y                         
CLIN_CODE_MODIFIER      VARCHAR2(13) Y

and Connor said...

Is it possible the constraint was added later ? or is notvalidated ?

eg


SQL> drop table t purge;

Table dropped.

SQL> create table t (
  2  PATIENT_ID              NUMBER        not null,
  3  RECORD_ID               NUMBER        ,
  4  CLIN_CODE_POSITION      NUMBER        ,
  5  CLIN_CODE_TYPE          VARCHAR2(20)  ,
  6  CLIN_CODE               VARCHAR2(36)  ,
  7  CLIN_CODE_MODIFIER_TYPE VARCHAR2(13) ,
  8  CLIN_CODE_MODIFIER      VARCHAR2(13) );

Table created.

SQL>
SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 PARTITION BY HASH (PATIENT_ID) partitions 4 as select * from t where 1=2;

Table created.

SQL> desc t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 PATIENT_ID                    NOT NULL NUMBER
 RECORD_ID                              NUMBER
 CLIN_CODE_POSITION                     NUMBER
 CLIN_CODE_TYPE                         VARCHAR2(20)
 CLIN_CODE                              VARCHAR2(36)
 CLIN_CODE_MODIFIER_TYPE                VARCHAR2(13)
 CLIN_CODE_MODIFIER                     VARCHAR2(13)

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t (
  2  PATIENT_ID              NUMBER        ,
  3  RECORD_ID               NUMBER        ,
  4  CLIN_CODE_POSITION      NUMBER        ,
  5  CLIN_CODE_TYPE          VARCHAR2(20)  ,
  6  CLIN_CODE               VARCHAR2(36)  ,
  7  CLIN_CODE_MODIFIER_TYPE VARCHAR2(13) ,
  8  CLIN_CODE_MODIFIER      VARCHAR2(13)  );

Table created.

SQL>
SQL> alter table t modify patient_id not null enable novalidate;

Table altered.

SQL>
SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 PARTITION BY HASH (PATIENT_ID) partitions 4 as select * from t where 1=2;

Table created.

SQL> desc t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 PATIENT_ID                             NUMBER
 RECORD_ID                              NUMBER
 CLIN_CODE_POSITION                     NUMBER
 CLIN_CODE_TYPE                         VARCHAR2(20)
 CLIN_CODE                              VARCHAR2(36)
 CLIN_CODE_MODIFIER_TYPE                VARCHAR2(13)
 CLIN_CODE_MODIFIER                     VARCHAR2(13)

SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mike, August 03, 2016 - 2:46 pm UTC

There's a primary key constraint on the source table, which includes patient_id. It is validated.

This is the DDL:
alter table source_table
  add constraint PK_MEDCLM_CLIN_CD primary key (PATIENT_ID, RECORD_ID, CLIN_CODE_POSITION, CLIN_CODE_TYPE)
  using index 
  local;

Chris Saxon
August 04, 2016 - 12:46 pm UTC

You can make a column non-null explicitly with a not null constraint. Or it can inherit this property by being part of a primary key. I suspect you don't have an explicit not null constraint on the source:

SQL> create table t (
  2    x int not null primary key
  3  );

Table created.

SQL>
SQL> create table t1 as
  2    select * from t where 1=0;

Table created.

SQL>
SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                 NOT NULL NUMBER(38)

SQL> desc t1
 Name              Null?    Type
 ----------------- -------- ------------
 X                 NOT NULL NUMBER(38)

SQL>
SQL> drop table t purge;

Table dropped.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t (
  2    x int primary key
  3  );

Table created.

SQL>
SQL> create table t1 as
  2    select * from t where 1=0;

Table created.

SQL>
SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                 NOT NULL NUMBER(38)

SQL> desc t1
 Name              Null?    Type
 ----------------- -------- ------------
 X                          NUMBER(38)


So what's the difference? If you drop a primary key, the column is only not null if you had an explicit not null constraint on it too:

SQL> alter table t drop primary key;

Table altered.

SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                          NUMBER(38)

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t (
  2    x int not null primary key
  3  );

Table created.

SQL> alter table t drop primary key;

Table altered.

SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                 NOT NULL NUMBER(38)


Chris

Mike, August 04, 2016 - 12:57 pm UTC

That's helpful, thank you!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.