3 Replies Latest reply: Apr 7, 2010 7:05 AM by 469636 RSS

    Create table as select

    469636
      in the column definition it can a default value or integrity constraint I read that in oracle book but do not understand can you kindly give example
        • 1. Re: Create table as select
          EdStevens
          Samim wrote:
          in the column definition it can a default value or integrity constraint I read that in oracle book but do not understand can you kindly give example
          Huh?

          What is it you want to do?

          Are you wanting to create and populate a table by using CREATE TABLE AS SELECT, and also put some constraints and default values on certain columns of the new table?
          • 2. Re: Create table as select
            729338
            Hi,

            Your question is not very clear. Please read it yourself to find out if it is worded right?

            create table as select (CTAS) does not copy constraints, PK, default values etc on its own. To have constraints etc as part of CTAS you need to have complete create table statement along with all constraints, indexes, check or default values.

            e.g.

            create table tab1 (col1 number primary key, col2 char(1) default 'Y', col3 number) as select a,b,c from tab2;

            Regards
            • 3. Re: Create table as select
              469636
              Your example have answered my questions

              The only wrong think in your example is:- ORA-01773: may not specify column datatypes in this CREATE TABLE

              Thanks