Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

References Integration with table

Status
Not open for further replies.

amolso

Programmer
Sep 8, 2000
71
IN
Hello Friends
My query is pretty long

I have a table 'COMPANY' which is a master table and a lot of other table depend on it (That way a lot of constraints)
Now if I want to recreate this table witout affecting data.
I want to do this bcos of in order to modify order of the fields and also to add some columns. Oracle Doesnt allow you to drop table as other table depend on it.

If I create the DUMMY table with
'CREATE TABLE COMPANY_TEMP AS
SELECT * FROM COMPANY'
it will create the tables with data but it will not copy the constraints. And Also the original table cannot be dropped. Therefore
1) How can I recreate table with Constrains same as previous
table ?
2) How can I drop the Table like this without disabling each
and every constraint

Thanks in Advance
Amol [sig][/sig]
 
1) It is difficult to recreate the constraints since the constraints are FROM other tables to COMPANY. You could look at the tables USER_CONSTRAINTS and USER_CONS_COLUMNS. These are data dictionary views of the constraints on every table owned by the current user. To see all the constraints that would have to be recreated, use a query like:
Code:
SELECT * 
FROM user_constraints
WHERE constraint_type = 'R'
  AND r_constraint_name = <pk_constraint>
pk_constraint is the *name* of the primary key constraint on the COMPANY table. If there are any unique constraints on the COMPANY table, create a second query that specifies the unique constraint name.

2) Consider the command:
Code:
 DROP TABLE COMPANY CASCADE CONSTRAINTS;

However, as an alternative to recreating the table, you can add columns to a table with a command like the following example:
Code:
ALTER TABLE COMPANY 
  ADD (credit_rating VARCHAR2(20));

As a general piece of knowledge, the order of columns in a table has no effect on the functionality. Some sites like to have the columns in a particular order for savings in disk space, but if all the columns of a table are required (NOT NULL) then the order of columns doesn't effect disk space either.

Perhaps you can give more information about the task you are trying to accomplish.
 
Do your constraints have to have the same name as the orginally, i.e. are they named by the database? If the names do not matter, then simply create you table and do an straight insert with your SELECT altered to suit your new table. The other option, and one that I use quite a bit, is to download a copy of TOAD (Tool for Oracle Application Developers). This has an option to automatically (well nearly so) to drop and re-create a modified table with the same named constraints replaced. It takes care of all foreign key references for you and compiling of dependant objects. There is a freeware version of TOAD, see I suspect that Oracle's OEM (Oracle Enterprise Manager) does the same sort of thing, just with the dreadful Oracle GUI that accompanys almost all their products.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top