Printable View
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
create the table as you want it, then insert /*+APPEND*/ into ....
Quote:
Originally Posted by davey23uk
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
Not true, a CREATE..AS SELECT... will allways create the same NOT NULL constraints as original table. :rolleyes:Quote:
...not exactly as the NOT NULL constraints from the original table
Quote:
Originally Posted by dgcampos
I mean pre-create the table without any constraints, then insert into it
create table int_segurohogar_BAK as (select * from int_segurohogar where 1=2);Quote:
Originally Posted by LKBrwn_DBA
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.
Quote:
Originally Posted by dgcampos
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;
WRONG, TOAD is lying:Quote:
Originally Posted by dgcampos
:DCode: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 :Quote:
Originally Posted by LKBrwn_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
No problem, no need to apologize. :D
Possibly modifying the columns as NULL might be a simpler approach than looking up the system names of the NOT NULL constraints and disabling them:
Code:SQL> CREATE TABLE testit (col1 INT NOT NULL, col2 INT NOT NULL);
Table created.
SQL> set lines 50
SQL> desc testit
Name Null? Type
----------------------- -------- ----------------
COL1 NOT NULL NUMBER(38)
COL2 NOT NULL NUMBER(38)
SQL> ALTER TABLE testit
2 MODIFY (col1 NULL, col2 NULL);
Table altered.
SQL> desc testit
Name Null? Type
----------------------- -------- ----------------
COL1 NUMBER(38)
COL2 NUMBER(38)
SQL>
You can override the not null constraint creation easily enough by just listing the column names with a "null" after them ...
Code:drop table t1;
drop table t2;
create table t1 (col1 number not null);
insert into t1 values (1);
create table t2 (col1 null) as select * from t1;
desc t2