如何在11g中的select语句中为新创建的表的列设置默认值 [英] How to set default value for column of new created table from select statement in 11g

问题描述

我在Oracle 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);

创建成功.由于某些原因，我需要使用相同的旧表结构和数据创建另一个表.所以我创建了一个名为abc的新表

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. 

此处成功创建了具有与旧表xyz相同的结构和数据的"abc".但是对于旧表"xyz"中的工资"列，默认值设置为"0".但是在新创建的表"abc"中未设置默认值.

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.

这一切在Oracle 11g中.请告诉我为什么未设置默认值的原因，以及如何使用select语句设置默认值.

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.

推荐答案

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

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.

这篇关于如何在11g中的select语句中为新创建的表的列设置默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！