使用select union创建表没有约束 [英] create table with select union has no constraints

问题描述

我使用select和union创建了一个表，如下所示:

I created a table using select with a union, as follows:

create table tableC as
select column1, column2 from tableA
union all
select column1, column2 from tableB

结果表(tableC)没有继承任何tableA或tableB的约束.为什么没有将约束复制到新表中?

The resulting table (tableC) has inherited none of the constraints from tableA or tableB. Why weren't the constraints copied to the new table?

推荐答案

使用select ... as ...创建表从不复制约束.如果希望新表继承原始表的约束，则必须手动创建新约束.

Using select ... as ... to create a table never copies constraints. If you want the new table to inherit constraints from the original tables, you must create the new constraints manually.

正如@Davek指出的那样，not null约束将从单个表select ... as ...中复制.我想那是因为它们既是列属性又是约束.但是，一旦该列有多个来源，Oracle就不会尝试应用该约束是合理的.

As @Davek points out, not null constraints will get copied from a single table select ... as .... I imagine that's because they are both column attributes and constraints. However, once the column has more than one source, it is reasonable that Oracle would not try to apply that constraint.

针对后续问题在CTAs之后是否有可能从tableA或tableB中赋予tableC相同的约束?":

In response to the follow-up question "would it be possible to give tableC the same constraints either from tableA or tableB, after a CTAs?":

当然可以，但是没有单个命令可以做到.您可以编写一个使用动态SQL复制约束的过程.但是，除非希望自动执行此操作，否则通常使用IDE提取DDL并更改表名通常会更容易.

Of course it's possible, but there's no single command to do it. You could write a procedure that used dynamic SQL to copy the constraints. However, unless you're looking to automate this behavior, it'll generally be easier to extract the DDL using an IDE and change the table name.

这篇关于使用select union创建表没有约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！