Re: not null column during CTAS(not null definition did not come across)

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 5 Mar 2002 11:18:58 -0800
Message-ID: <a635n20dqf@drn.newsguy.com>

In article <44a19320.0203051045.47eb2c46_at_posting.google.com>, tedchyn_at_yahoo.com says...
>
>all,
>
>when creating a table using CTAS, I found not null definition on one of the
>column did not propagate(see below). My question:
>1. why some of not null column propagate whereas other column does not ?
>

must be more then meets the eye. In

716, 734, 806, 815, 817, 90

I find:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table contacts   2 (

  3   ACCOUNT_ID                      NUMBER(10) NOT NULL,
  4   ID                              NUMBER(22) NOT NULL,
  5   CLIENT_DATA                              VARCHAR2(20),
  6   NODE_START_DATE                          DATE,
  7   NODE_END_DATE                            DATE,
  8   ORIGIN_ORDER_ID                          VARCHAR2(14),
  9   CONTACT_TYPE                             VARCHAR2(50),
 10   NAME                                     VARCHAR2(25),
 11   CITY                                     VARCHAR2(25),
 12   TELEPHONE                                VARCHAR2(17),
 13   EMAIL_ADDR                               VARCHAR2(17),
 14   SUBGROUP_TYPE_ID                NUMBER(10) NOT NULL

 15 )
 16 /

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table contacts_new   2 tablespace users pctfree 0 storage   3 (initial 1m next 1m pctincrease 0) as select * from   4 contacts where 1=2;

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc contacts;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 ACCOUNT_ID                          NOT NULL NUMBER(10)
 ID                                  NOT NULL NUMBER(22)
 CLIENT_DATA                                  VARCHAR2(20)
 NODE_START_DATE                              DATE
 NODE_END_DATE                                DATE
 ORIGIN_ORDER_ID                              VARCHAR2(14)
 CONTACT_TYPE                                 VARCHAR2(50)
 NAME                                         VARCHAR2(25)
 CITY                                         VARCHAR2(25)
 TELEPHONE                                    VARCHAR2(17)
 EMAIL_ADDR                                   VARCHAR2(17)
 SUBGROUP_TYPE_ID                    NOT NULL NUMBER(10)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc contacts_new;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 ACCOUNT_ID                          NOT NULL NUMBER(10)
 ID                                  NOT NULL NUMBER(22)
 CLIENT_DATA                                  VARCHAR2(20)
 NODE_START_DATE                              DATE
 NODE_END_DATE                                DATE
 ORIGIN_ORDER_ID                              VARCHAR2(14)
 CONTACT_TYPE                                 VARCHAR2(50)
 NAME                                         VARCHAR2(25)
 CITY                                         VARCHAR2(25)
 TELEPHONE                                    VARCHAR2(17)
 EMAIL_ADDR                                   VARCHAR2(17)
 SUBGROUP_TYPE_ID                    NOT NULL NUMBER(10)

happens consistently. Whats the original create table look like for contacts, can you reproduce this with a standalone testcase?

>
>SQL> desc sda_mcr.contacts;
> Name Null? Type
> ------------------------------- -------- ----
> ACCOUNT_ID NOT NULL NUMBER(10)
> ID NOT NULL NUMBER(22)
> CLIENT_DATA VARCHAR2(20)
> NODE_START_DATE DATE
> NODE_END_DATE DATE
> ORIGIN_ORDER_ID VARCHAR2(14)
> CONTACT_TYPE VARCHAR2(50)
> NAME VARCHAR2(25)
> CITY VARCHAR2(25)
> TELEPHONE VARCHAR2(17)
> EMAIL_ADDR VARCHAR2(17)
> SUBGROUP_TYPE_ID NOT NULL NUMBER(10)
>
>
>SQL> create table SDA_MCR_ARCH.CONTACTS_new nologging
> 2 tablespace MCR_ARCH_MD_DATA_1 pctfree 0 storage
> 3 (initial 1m next 1m pctincrease 0) as select * from
> 4 sda_mcr.contacts where 1=2;
>
>Table created.
>
>SQL> desc sda_mcr_arch.contacts_new;
> Name Null? Type
> ------------------------------- -------- ----
> ACCOUNT_ID NOT NULL NUMBER(10)
> ID NOT NULL NUMBER(22)
> CLIENT_DATA VARCHAR2(20)
> NODE_START_DATE DATE
> NODE_END_DATE DATE
> ORIGIN_ORDER_ID VARCHAR2(14)
> CONTACT_TYPE VARCHAR2(50)
> NAME VARCHAR2(25)
> CITY VARCHAR2(25)
> TELEPHONE VARCHAR2(17)
> EMAIL_ADDR VARCHAR2(17)
> SUBGROUP_TYPE_ID NUMBER(10) # not null missing

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Mar 05 2002 - 13:18:58 CST