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

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开发者_C百科 can set this using select statement.

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.

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

You will need to alter table abc modify (salary default 0);

new table inherits only "not null" constraint and no other constraint. Thus you can alter the table after creating it with "create table as" command or you can define all constraint that you need by following the

create table t1 (id number default 1 not null);
insert into t1 (id) values (2);

create table t2 as select * from t1;

This will create table t2 with not null constraint. But for some other constraint except "not null" you should use the following syntax

create table t1 (id number default 1 unique);
insert into t1 (id) values (2);

create table t2 (id default 1 unique)
as select * from t1;