oracle 如何从 11g 中的 select 语句中为新创建的表的列设置默认值

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

提问by gitee.com

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

我在 Oracle 11g 中创建了一个表，其中一列具有默认值。语法是：

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 abcas

这样就创建成功了。由于某些原因，我需要创建另一个具有相同旧表结构和数据的表。所以，我创建了一个新的表名abc作为

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.

这里“abc”使用与旧表相同的结构和数据成功创建xyz。但是对于旧表“xyz”中的“salary”列，默认值设置为“0”。但是在新创建的表“abc”中没有设置默认值。

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.

这一切都在 Oracle 11g 中。请告诉我未设置默认值的原因以及我们如何使用 select 语句设置它。

回答by Gary Myers

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

您可以在 CREATE TABLE AS SELECT 中指定约束和默认值，但语法如下

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.

也就是说，它不会从源表/选择继承约束。只有数据类型（长度/精度/小数位数）由选择决定。

回答by Frank Schmitt

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

原因是CTAS（Create table as select）不会从源表复制任何元数据到目标表，即

• 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

• 使用dbms_metadata.get_ddl获取完整的表结构，将表名替换为新的表名，执行这条语句，然后做一个INSERT复制数据
• 或者继续使用 CTAS，从 user_constraints 中提取源表的非空约束，然后将它们添加到目标表中

回答by Colin 't Hart

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

你需要 alter table abc modify (salary default 0);

回答by user3182853

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 as”命令创建表后更改表，或者您可以按照以下命令定义所需的所有约束

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

这将创建具有非空约束的表 t2。但是对于除“not null”之外的其他一些约束，您应该使用以下语法

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;