"Create table as select" 不能保留非空约束

Question

我正在尝试使用Oracle的"Create Table As Select"功能进行快速更新。我遇到的问题是"Null"字段没有被保留。

我定义了以下表格：

create table mytable(
  accountname varchar2(40) not null,
  username varchar2(40)
 );

当我执行原始的CTAS时，account上的NOT NULL被保留：

 create table ctamytable as select * from mytable;

 describe ctamytable;

Name        Null     Type         
----------- -------- ------------ 
ACCOUNTNAME NOT NULL VARCHAR2(40)

USERNAME             VARCHAR2(40) 

然而，当我对accountname进行替换时，NOT NULL限制并未保留。

 create table ctamytable as 
   select replace(accountname, 'foo', 'foo2') accountname, 
          username 
     from mytable;

 describe ctamytable;

Name        Null Type          
----------- ---- ------------- 
ACCOUNTNAME      VARCHAR2(160) 
USERNAME         VARCHAR2(40) 

注意到accountname字段不再为空，而varchar2字段从40个字符变为了160个字符。有人之前看到过吗？

- Joe Devilla

Answer 1

这是因为您不再选择具有列定义和元数据的ACCOUNTNAME。相反，您正在选择一个字符串，即replace函数的结果，它没有任何元数据。这完全是不同的数据类型。

可能更好的方法是使用原始列的查询创建表，但带有保证为0行的WHERE子句。

然后，您可以使用实际的SELECT正常地将其插入到表中。

通过具有0行的查询，您仍将获得列元数据，因此应该创建表，但不会插入任何行。确保使您的WHERE子句快速，例如WHERE primary_key = -999999，一些您知道永远不存在的数字。

- Will Hartung

---

不需要使用魔法数字来匹配一行，只需将 WHERE 子句设置为 1=2。 - Ben

谢谢，类型转换的解释很有道理。我正在尝试进行模式全局比较，未显示 NOT NULL 作为误报。我需要找到另一种确定 Null 列的方法。 - Joe Devilla

@Ben 是的，那是我们在旧时代所使用的方法，但我不确定它是否普遍适用于其他数据库。 - Will Hartung

Answer 2

另一种选择是在调用CREATE TABLE AS SELECT时定义列。 可以列出列名并包括约束条件，同时排除数据类型。

以下是示例：

```sql CREATE TABLE new_table (col1, col2, col3) AS SELECT old_col1, old_col2, old_col3 FROM old_table; ```

create table ctamytable (
  accountname not null,
  username
)
as 
  select 
    replace(accountname, 'foo', 'foo2') accountname, 
    username 
  from mytable;

请注意，尽管此语法是有效的，但您不能包含数据类型。另外，显式声明所有列有些削弱了使用CREATE TABLE AS SELECT的目的。

- Mike Meyers

---

再次阅读问题后，我意识到我并没有真正解决你所遇到的原始问题，但它确实提供了有关使用CTAS的额外信息。 - Mike Meyers

谢谢Mike。我是通过Java完成所有这些操作的。最后，在运行CTAS之前，我会存储所有的NOT NULL约束，并在之后重新应用它们。 - Joe Devilla