CODEWITHSUNDEEP
oracle-databaseoracle11g
gitee.com
gitee.com

Reputation: 426

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 can set this using select statement.

Upvotes: 22

Views: 153916

Answers (4)

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 35

user3182853
user3182853

Reputation: 21

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;

Upvotes: 2

Colin 't Hart
Colin 't Hart

Reputation: 7749

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

Upvotes: 6

Frank Schmitt
Frank Schmitt

Reputation: 30845

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

Upvotes: 9

Related Questions