Skip to Main Content
  • Questions

  • Can we do a CTAS (Create table as) without the NOT NULL constraints?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Manas.

Asked: July 04, 2016 - 1:42 pm UTC

Last updated: July 05, 2016 - 4:49 pm UTC

Version: 12.0.2.0/11.2.0.3.0

Viewed 10K+ times! This question is

You Asked

Can we do a CTAS (Create table as) and create the new table without the NOT NULL constraints?

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

drop table a;

create table a ( a NUMBER NOT NULL
, b VARCHAR2(10)
);

desc a

TABLE a
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B VARCHAR2(10)

create table b as
select * from a;

desc b

TABLE b
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B VARCHAR2(10)

create table c as
select a,b from a;

desc c

TABLE c
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B VARCHAR2(10)

create table d(a,b) as
select a,b from a;

desc d

TABLE d
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B VARCHAR2(10)


I do not want the NOT NULL constraint in the newly created table. Is it possible?


and Chris said...

Yep. Just declare the constraints in the column list of the table you're creating:

create table a ( a number not null , b varchar2 ( 10 ) ) ;

desc a

Name Null     Type         
---- -------- ------------ 
A    NOT NULL NUMBER       
B             VARCHAR2(10) 

create table b ( a null, b null) as
select * from a;

desc b

Name Null Type         
---- ---- ------------ 
A         NUMBER       
B         VARCHAR2(10)

You can also add other constraints, make a nullable column non-null if you want:

drop table b purge;

create table b ( a null unique, b not null) as
select * from a;

desc b

Name Null     Type         
---- -------- ------------ 
A             NUMBER       
B    NOT NULL VARCHAR2(10)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 04, 2016 - 4:51 pm UTC

Ah my bad!
So I have a script, something like-

SELECT 'CREATE TABLE ' || table_name || '_bkup AS SELECT * FROM ' || table_name ||';'
FROM user_tables;

Now the query that would be generated would be something like -
CREATE TABLE a_bkup AS SELECT * FROM a;

Within the statement above is there any Oracle keyword with which it is ensured that none of the tables have NOT NULL constraints.

I understand that I can do workarounds, say fetch from a combination of user_tables & user_tab_columns etc. Or Alter all NOT NULL columns to NULL, but is their something that Oracle provides.
Chris Saxon
July 05, 2016 - 1:00 am UTC

No, but with LISTAGG, the conversion is pretty easy

SELECT 'CREATE TABLE ' || t.table_name || '_bkup ('||
        ( select listagg(column_name||' null',',') within group ( order by column_id )
          from   user_tab_columns
          where  table_name = t.table_name )||
        ') AS SELECT '||
        ( select listagg(column_name,',') within group ( order by column_id )
          from   user_tab_columns
          where  table_name = t.table_name )||
        ' FROM ' || t.table_name ||';' 
FROM user_tables t;


Just be aware that in these days of hidden columns, unused columns, and invisible colums, your "backup" tables could (internally) be quite different to your source tables.

Hope this helps.

another solution

A reader, July 05, 2016 - 12:51 pm UTC

... to the original question would be
create table d as select cast(a as number) as a, b from a;



Just declare the constraints in the column list of the table you're creating:

There are no constraints on b ?
Chris Saxon
July 05, 2016 - 4:49 pm UTC

Nice, thanks.

There are no constraints on b ?

? I just meant you can apply whatever constraints you want in your CTAS (null, not null, unique, check, ...).
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.