Thanks for the question, Pravin.

Asked: January 11, 2018 - 9:52 am UTC

Last updated: January 11, 2018 - 3:43 pm UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am creating table "tmp_pks1" with SID as DEFAULT 1.

SQL> CREATE TABLE tmp_pks1(SID NUMBER DEFAULT 1, NAME VARCHAR2(50) NOT NULL);

Table created.

SQL> CREATE TABLE tmp_pks2 AS SELECT * FROM tmp_pks1;

Table created.

SQL> DESC temp_pks2;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 NAME                                      NOT NULL VARCHAR2(50 CHAR)

table tmp_pks2 is created using tmp_pks1 but there is no default imposed on field SID as it is in table tmp_pks1.
could you guild me what I need change/add in <CREATE TABLE tmp_pks2 AS SELECT * FROM tmp_pks1;> also get DEFAULT imposed on field SID.

Thanks,
Pravin

and Chris said...

Create table as select only copies not null constraints from the source table. Any other properties such as column defaults you need to specify yourself. You can do this in the column clause:

CREATE TABLE tmp_pks1(SID NUMBER DEFAULT 1, NAME VARCHAR2(50) NOT NULL);

CREATE TABLE tmp_pks2 (sid default 1, name) AS 
  SELECT * FROM tmp_pks1;

Side note: desc doesn't show default values! But info in SQLcl and SQL Developer does:

desc tmp_pks2  

Name Null?    Type         
---- -------- ------------ 
SID           NUMBER       
NAME NOT NULL VARCHAR2(50) 

info tmp_pks2 

TABLE: TMP_PKS2 
  LAST ANALYZED: 
  ROWS         : 
  SAMPLE SIZE  : 
  INMEMORY     :DISABLED 
  COMMENTS     : 

Columns 
NAME         DATA TYPE           NULL  DEFAULT    COMMENTS
 SID         NUMBER              Yes   1              
 NAME        VARCHAR2(50 BYTE)   No

Rating

(3 ratings)

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

Comments

in 12.2 with using FOR EXCHANGE WITH Table option

Rajeshwaran, Jeyabal, January 11, 2018 - 11:21 am UTC

using an option available from 12.2 we can copy the column level defaults to a new table.

but no data copy possible.

demo@ORA12C> create table t1(x int default 1, y varchar2(30) not null, z date) ;

Table created.

demo@ORA12C> insert into t1(x,y,z) values(default,'ABC',sysdate);

1 row created.

demo@ORA12C> insert into t1(y,z) values('Hello',sysdate-3);

1 row created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select * from t1;

         X Y                              Z
---------- ------------------------------ -----------
         1 ABC                            11-JAN-2018
         1 Hello                          08-JAN-2018

demo@ORA12C> create table t2
  2  for exchange with table t1;

Table created.

demo@ORA12C> select table_name, column_name, data_type,data_length,nullable,data_default
  2  from user_tab_columns
  3  where table_name in ('T1','T2')
  4  order by table_name,column_id;

TABLE COLUM DATA_TYPE  DATA_LENGTH N DATA_DEFAU
----- ----- ---------- ----------- - ----------
T1    X     NUMBER              22 Y 1
T1    Y     VARCHAR2            30 N
T1    Z     DATE                 7 Y
T2    X     NUMBER              22 Y 1
T2    Y     VARCHAR2            30 N
T2    Z     DATE                 7 Y

6 rows selected.

demo@ORA12C> select * from t2;

no rows selected

demo@ORA12C> insert into t2 select * from t1;

2 rows created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select * from t2;

         X Y                              Z
---------- ------------------------------ -----------
         1 ABC                            11-JAN-2018
         1 Hello                          08-JAN-2018

demo@ORA12C>

January 11, 2018 - 3:43 pm UTC

Yes, good point.

Pravin Shrivastava, January 11, 2018 - 1:01 pm UTC

Pravin Shrivastava, January 12, 2018 - 7:44 am UTC

Thanks Chris for reply.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

in 12.2 with using FOR EXCHANGE WITH Table option

More to Explore

Design

Create table using existing table and copy column defaults

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

in 12.2 with using FOR EXCHANGE WITH Table option

More to Explore

Design