Table constraints

How to delete multiple constraints in one statement? or
How to copy a table structure without constraints?

We are using SQLLOAD to update some existing tables. Data is written to ascii file from our legacy system. We then need to update the Oracle tables with this data.

Since sqlload will NOT update, I load the data into a temporary table. I then use the standard SQL UPDATE command to update the fields in the original table.

Some of our data has null fields that gets inserted into NOT NULL fields. This causes the overall exercise not to work. I have been deleting these constraints one by one.

select constraint_name
from all_constraints

alter table table_name
drop constraint 'constraint_name'
where table = 'table_name'

I then have to add a primary key.

Doing this one by one is quite time consuming. Is there a way to:
- delete all the constraints in one statement?

• create the temporary table with no constraints?

p.s.
I create the temporary table with:

create table table_name2 as
(select *
from table_name
where primary key is null)

• it gives a table with no records, but all the fields, and constraints (except the primary keys, and indexes).
• maybe there is a better way?

Rodger Lepinsky
ADP Systems Partnership
Winnipeg, Manitoba, Canada Received on Wed May 28 1997 - 00:00:00 CDT