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

In article <44a19320.0203051717.223ba9cb_at_posting.google.com>, tedchyn_at_yahoo.com says...
>
>thomas, thnx for the reply.
>
>1. yes I can reproduce in three other tables.
>2. the original table has subgroup_type_id nullable and it was altered to
> not null and include in part of primary key.
>3. under the above described conditions I can not reproduce this bug in a test
> table within the same instance.
>
>thnx ted
>
>

look at that -- unless you put an explicit NOT NULL constraint -- the NOT NULL that comes from being part of a primary key doesn't carry over on the CTAS.

As far back as 716 -- none of the tables T2 will have any NOT NULL constraints, but T will:

create table t ( x int, y int );
alter table t add constraint t_pk primary key(x,y); create table t2 as select * from t;

create table t ( x int, a int, y int, constraint t_pk primary key(x,y) ); create table t2 as select * from t;

create table t ( x int primary key, y int ); create table t2 as select * from t;

Not unless you add an explicit NOT NULL constraint to the table itself. So the CTAS is copying some constraints and since there is no NOT NULL constraint to copy with just a primary key (there is a primary key constraint, not a not null constraint) it doesn't copy it.

>
>Thomas Kyte <tkyte_at_oracle.com> wrote in message
>news:<a635n20dqf_at_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