-
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
-
create the table as you want it, then insert /*+APPEND*/ into ....
-
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
-
...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
-
Originally Posted by dgcampos
what do you mean by that?
dgc
I mean pre-create the table without any constraints, then insert into it
-
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.
-
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;
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|