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

User · Answer

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

User · Answer

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

User · Answer

You will need to alter table abc modify  salary default 0

User · Answer

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

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

Examples related to oracle

Examples related to oracle11g