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

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 - 11:59:04 CST