`NOT NULL` constraints passed while creating new table from existing table?

Question

I'm trying to create a table from an existing table. What are the other constraints that are passed apart from the NOT NULL constraints? Also what are the other attributes/features passed to the new table?

The query that I use to create the new table is this:

create table sales_emp
as
select * from emp;

hi,thanks for the response.i'm trying to create new table from oracle DBMS as follows create table sales_emp as select* from emp; where sales_emp and emp tables structures are same.then in this case which constraints will be passed to the new table created from the existing table?and what are the other features inherited to the new table from the existing one? — Mittu
– Mittu, Commented Jul 23, 2012 at 6:39
I added your query to your post and also added the oracle tag to make your question look clearer and more specific without having to refer to the comments section. If you think you've forgotten to add some other details that might be important, please feel free to edit them in too (using the edit link under your question). — Andriy M
– Andriy M, Commented Jul 23, 2012 at 9:35
According to your comment,i understand that apart from NOT NULL constraints,even the other contraints too are not carried over to the new table.but recently came across a question from 1z0-051 exam,here the answer says differnt.Please refer the below link for the question scribd.com/doc/75690829/… a bit ambiguity.Please clarify — Mittu
– Mittu, Commented Jul 23, 2012 at 21:09
@Mittu: What particularly seems ambiguous to you? Presently I can see no ambiguity. — Andriy M
– Andriy M, Commented Jul 23, 2012 at 21:23

Community · Accepted Answer · 2020-06-20 09:12:55Z

The following information can be found in the official manual (all the emphases are mine):

Oracle Database derives datatypes and lengths from the subquery. Oracle Database follows the following rules for integrity constraints and other column and table attributes:

Oracle Database automatically defines on columns in the new table any NOT NULL constraints that were explicitly created on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column. If any rows violate the constraint, then the database does not create the table and returns an error.

NOT NULL constraints that were implicitly created by Oracle Database on columns of the selected table (for example, for primary keys) are not carried over to the new table.

In addition, primary keys, unique keys, foreign keys, check constraints, partitioning criteria, indexes, and column default values are not carried over to the new table.

If the selected table is partitioned, you can choose whether the new table will be partitioned the same way, partitioned differently, or not partitioned. Partitioning is not carried over to the new table. Specify any desired partitioning as part of the CREATE TABLE statement before the AS subquery clause.

Collectives™ on Stack Overflow

`NOT NULL` constraints passed while creating new table from existing table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related