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

I think what Thomas means is the other way around. The 'NOT NULL' you see in the DESCRIBE will show up there in two cases:

1. you have put explicitly a NOT NULL constraint on the column
2. the column is part of a PK

In the first case this will copy over, in the second case it won't. My guess is (I can't check this at the moment) that in the first case you will find the NOT NULL constraint in USER_CONSTRAINTS or USER_CONS_COLUMNS, in the second case you won't (but you will find the PK of course).

Jaap.

On Wed, 06 Mar 2002 17:59:04 GMT, "RSH" <RSH_Oracle_at_worldnet.att.net> wrote:

>So a plain garden variety table definition that has a NOT NULL constraint on
>it won't propagate, unless its a PK which is NOT NULL by definition?
>
>I've done more CTASes that I can count, I guess I just never ran into this
>happening..huh. we sure had lots of NOT NULL columns. Guess either luck or
>something, either I never CTASed such a table or my programmers and my DBA's
>and I never noticed this.
>
>Thanks for the tip, I will DEFINITELY watch out for it.
>
>RSH.
>
>"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
>news:a65een0258h_at_drn.newsguy.com...
>> 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
>>
>
>
Received on Wed Mar 06 2002 - 17:18:48 CST