Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> not null column during CTAS(not null definition did not come across)
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 ?
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? TypeReceived on Tue Mar 05 2002 - 12:45:29 CST
------------------------------- -------- ----
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