create table without constraints
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: create table without constraints

  1. #1
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25

    create table without constraints

    Hello everyone :

    any suggestions on how to create a table from a select without its constraints??? i have the following :

    create table int_segurohogar_BAK as (select * from int_segurohogar where 1=2);

    it creates the table but creates NOT NULL constraints on all the columns, not exactly as the NOT NULL constraints from the original table. How can i avoid creating this constraints????

    thank you very much for your support.

    regards,

    dgc

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    create the table as you want it, then insert /*+APPEND*/ into ....

  3. #3
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by davey23uk
    create the table as you want it, then insert /*+APPEND*/ into ....

    what do you mean by that? i want to create the table from a select without the NOT NULL constraints, APPEND hit only affects the watermark point when inserting. is it possible to disable the constraints after the create table statement? im trying with this with little result :

    alter table int_Segurovidaupgrade_BAK disable constraint SYS_C00274738;

    it disabled the constraint, but i need all the tables constraints.
    i need to do this in a cursor, any ideas?

    thank you very much for your support.


    regards,

    dgc

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Cool

    ...not exactly as the NOT NULL constraints from the original table
    Not true, a CREATE..AS SELECT... will allways create the same NOT NULL constraints as original table.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by dgcampos
    what do you mean by that?
    dgc

    I mean pre-create the table without any constraints, then insert into it

  6. #6
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by LKBrwn_DBA
    Not true, a CREATE..AS SELECT... will allways create the same NOT NULL constraints as original table.
    create table int_segurohogar_BAK as (select * from int_segurohogar where 1=2);

    thats how i am creating the table.

    insert into int_segurohogar_bak (select * from int_segurohogar where solicitud = nSolicitud);

    thats how i am trying to insert into the table.

    then i get :

    PL/SQL: ORA-00947: not enough values.

    AND the constraints are in ALL the columns, not from the original table (i seeing it in TOAD). I also thought about that.

  7. #7
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by dgcampos
    create table int_segurohogar_BAK as (select * from int_segurohogar where 1=2);

    thats how i am creating the table.

    insert into int_segurohogar_bak (select * from int_segurohogar where solicitud = nSolicitud);

    thats how i am trying to insert into the table.

    then i get :

    PL/SQL: ORA-00947: not enough values.

    AND the constraints are in ALL the columns, not from the original table (i seeing it in TOAD). I also thought about that.

    but i found a way to diable the constraints :

    declare

    sConstraint varchar2(200);
    cursor oCursor is
    select constraint_name , table_name
    from user_constraints
    where table_name in (
    upper('int_Segurovidaupgrade_BAK'),
    upper('int_Segurohogarupgrade_BAK'),
    upper('int_segurohogar_BAK'),
    upper('int_segurovida_BAK'),
    upper('ts_operacionsolicitud_BAK'),
    upper('ts_integrantesolicitud_BAK'),
    upper('ts_estadosolicitud_BAK'),
    upper('int_upgradevidaexportado_BAK'),
    upper('int_upgradehogarexportado_BAK'),
    upper('ts_productosolicitud_BAK'),
    upper('ts_solicitud_BAK'));


    sCons user_constraints.constraint_name%TYPE;
    stable user_constraints.table_name%TYPE;

    begin
    if not oCursor%isopen then
    open oCursor;
    loop
    fetch ocursor into scons, stable;
    exit when ocursor%NOTFOUND;
    execute immediate 'alter table ' || upper(stable) || ' disable constraint ' || scons ;
    end loop;
    close oCursor;
    end if;
    end;

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Cool

    Quote Originally Posted by dgcampos
    AND the constraints are in ALL the columns, not from the original table (i seeing it in TOAD). I also thought about that.
    WRONG, TOAD is lying:
    Code:
    SQL>desc EMP
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                     NOT NULL VARCHAR2(10)
     JOB                                       NOT NULL VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                    NOT NULL NUMBER(2)
    
    SQL>create table emp2
      2  as select * from emp where 1=0;
    
    Table created.
    
    SQL>desc emp2
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                     NOT NULL VARCHAR2(10)
     JOB                                       NOT NULL VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                    NOT NULL NUMBER(2)
    
    SQL>

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  9. #9
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by LKBrwn_DBA
    WRONG, TOAD is lying:
    Code:
    SQL>desc EMP
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                     NOT NULL VARCHAR2(10)
     JOB                                       NOT NULL VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                    NOT NULL NUMBER(2)
    
    SQL>create table emp2
      2  as select * from emp where 1=0;
    
    Table created.
    
    SQL>desc emp2
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                     NOT NULL VARCHAR2(10)
     JOB                                       NOT NULL VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                    NOT NULL NUMBER(2)
    
    SQL>

    Dear DBA :

    thank you for your support. You are right, it was an scripting problem. The table was created with a the same name with sufix _BAK but took another structure. I am sorry for the inconvenience.

    I could anyway disable the constraints, just in case anyone find it useful, with the anonymous begin end block.

    Best wishes,

    dgc

  10. #10
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Thumbs up


    No problem, no need to apologize.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width