How to set default value for column of new created table from select statement in 11g

Question

I create a table in Oracle 11g with the default value for one of the columns. Syntax is:

create table xyz(emp number,ename varchar2(100),salary number default 0); 

This created successfully. For some reasons I need to create another table with same old table structure and data. So I created a new table with name abc as

create table abc as select * from xyz.  

Here "abc" created successfully with same structure and data as old table xyz. But for the column "salary" in old table "xyz" default value was set to "0". But in the newly created table "abc" the default value is not set.

This is all in Oracle 11g. Please tell me the reason why the default value was not set and how we can set this using select statement.

Answer 1

You can specify the constraints and defaults in a CREATE TABLE AS SELECT, but the syntax is as follows

create table t1 (id number default 1 not null); insert into t1 (id) values (2);  create table t2 (id default 1 not null) as select * from t1; 

That is, it won't inherit the constraints from the source table/select. Only the data type (length/precision/scale) is determined by the select.

Answer 2

The reason is that CTAS (Create table as select) does not copy any metadata from the source to the target table, namely

• no primary key
• no foreign keys
• no grants
• no indexes
• ...

To achieve what you want, I'd either

• use dbms_metadata.get_ddl to get the complete table structure, replace the table name with the new name, execute this statement, and do an INSERT afterward to copy the data
• or keep using CTAS, extract the not null constraints for the source table from user_constraints and add them to the target table afterwards