sql - 创建表的副本并在创建时为其提供约束

Question

我是 Oracle 数据库的新手。我知道我们可以使用创建表的副本创建表 copy_emp(eid、ename、job、mid、sal、dept_id)
作为 SELECT employee_id, concat(first_name,last_name),job_id,manager_id,salary,department_id FROM employees;稍后我们可以使用alter table copy_emp add constraint epk FORIEGN KEY(dept_id) references departments(dept_id) 添加外键约束

但是是否可以在通过查询创建表副本时提供约束。

例如我们可以做这样的事情吗:

CREATE table copy_emp(eid, ename,job,mid,sal,dept_id constraint dpt_fk references copy_dept(department_id) ON DELETEcascade)
as
SELECT employee_id,
concat(first_name,last_name),job_id,manager_id,salary,department_id 
FROM employees;

我们可以在创建表副本时提供约束吗？如果是，那么查询是什么？如果不是，那么为什么？

Answer 1

不，你不能那样做；你的第二个陈述(稍微固定)将得到

ORA-02440: Create as select with referential constraints not allowed
02440. 00000 -  "Create as select with referential constraints not allowed"
*Cause:    create table foo (... ref. con. ...) as select ...;
*Action:   Create the table as select, then alter the table to add the
           constraints afterwards.

这里提到in the documentation :

Restrictions on the Defining Query of a Table

The table query is subject to the following restrictions:

• The number of columns in the table must equal the number of expressions in the subquery.

• The column definitions can specify only column names, default values, and integrity constraints, not data types.

• You cannot define a foreign key constraint in a CREATE TABLE statement that contains AS subquery unless the table is reference partitioned and the constraint is the table's partitioning referential constraint. In all other cases, you must create the table without the constraint and then add it later with an ALTER TABLE statement.

可以提供其他约束，如评论中链接到的@Jeff 的答案，因此您可以添加主键:

CREATE table copy_emp(eid primary key, ename,job,mid,sal,dept_id)
as
SELECT employee_id,
concat(first_name,last_name),job_id,manager_id,salary,department_id 
FROM employees;

或者(只要约束名称是唯一的)

CREATE table copy_emp(eid, ename,job,mid,sal,dept_id,
  constraint emp_pk primary key (eid))
as
SELECT employee_id,
concat(first_name,last_name),job_id,manager_id,salary,department_id 
FROM employees;