Home » SQL & PL/SQL » SQL & PL/SQL » Create a table from another table with all constraints
Create a table from another table with all constraints [message #27666] |
Tue, 21 October 2003 23:42 |
Venugopalacharyulu kada
Messages: 13 Registered: February 2003
|
Junior Member |
|
|
Hello,
I need to CREATE table which will copy the constrains from the original table. Actually my problem is : I am using the query to CREATE a table from another table like
CREATE TABLE MASTER_DUMP AS SELECT * FROM MASTER;
It is creating MASTER_DUMP table without having the constrains like NOT NULL, PK, etc as in the MASTER table.
So, How can we create a table from another table with all constraints from the original table.
Bye
|
|
|
Re: Create a table from another table with all constraints [message #27675 is a reply to message #27666] |
Wed, 22 October 2003 08:41 |
Thiru
Messages: 1089 Registered: May 2002
|
Senior Member |
|
|
You can add the constraints later ..or you can just reverse engineer the CREATE TABLE statement from the MASTER table and change it to MASTER_DUMP and insert the data later..
You can use DBMS_METADATA.GET_DDL to get the create statement.
eg)
thiru@9.2.0:SQL>select dbms_metadata.get_ddl('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
CREATE TABLE "THIRU"."T"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
"GRADE" VARCHAR2(10),
CONSTRAINT "T_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
|
|
|
|
|
|
|
Re: Create a table from another table with all constraints [message #154631 is a reply to message #27675] |
Mon, 09 January 2006 19:33 |
Karthik143
Messages: 4 Registered: January 2006
|
Junior Member |
|
|
Hi,
I have a similar situation. Where I need to create a new table from an existing table. Sometimes I also need to add a new column apart from the columns in the existing table.
When I do this all the constraints are changed for the new tables. Is there a way to change this. The query used is given
CREATE TABLE tmp2 TABLESPACE DIM_DATA NOLOGGING PARALLEL 1 PCTFREE 0 PCTUSED 40 AS SELECT d.product_key product_key,
d.date_modified date_modified,
d.latest_flag latest_flag,
d.List_Price List_Price,
d.Manufacturer Manufacturer,
d.Price_Band Price_Band,
d.Product_Cost Product_Cost,
d.Product_Line Product_Line,
d.Product_Name Product_Name,
TO_NUMBER(0) Sales_Price, //(this is a new field added)
d.product_key_REAL product_key_REAL FROM ProductUpd_A d
Your suggestions will help in solving my problem.
This query is created in VB, based on the inputs the user is giving
Thanks
[Updated on: Mon, 09 January 2006 19:55] Report message to a moderator
|
|
|
Re: Create a table from another table with all constraints [message #154807 is a reply to message #154631] |
Tue, 10 January 2006 13:33 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Karthik143,
As previously explained and demonstrated by Thiru in his response to the similar situation, you can use dbms_metadata.get_ddl to extract the script for creating the original table, then just modify that script to change the name and add any new column(s) where you want. If you:
SELECT DBMS_METADATA.GET_DDL ('TABLE', 'PRODUCTUPD_A') FROM DUAL;
you will get the script for creating the productupd_a table with all of its constraints. Then just change productupd_a to temp2 and add any new column(s) where you want.
[Updated on: Tue, 10 January 2006 13:36] Report message to a moderator
|
|
|
|
Re: Create a table from another table with all constraints [message #154882 is a reply to message #154832] |
Wed, 11 January 2006 02:41 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
just to add,
Still DBMS_METADATA is very handy. Use GET_DEPENDENT_DDL.
scott@9i > select table_name,constraint_name,constraint_type,R_constraint_name from user_constraints;
TABLE_NAME CONSTRAINT_NAME C R_CONSTRAINT_NAME
------------------------------ ------------------------------ - ------------------------------
DEPT SYS_C001674 P
EMP SYS_C001675 C
EMP SYS_C001676 C
EMP SYS_C001677 P
EMP SYS_C001678 R SYS_C001674
scott@9i > @ddl.ddl
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER,
"ENAME" VARCHAR2(10) NOT NULL ENABLE,
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CHECK (sal between 1 and 5000) ENABLE,
PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 ENABLE,
FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
;
ALTER TABLE "SCOTT"."EMP" ADD FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
scott@9i > get ddl.ddl
1 set long 500000000000
2 set linesize 1000
3 set feed off;
4 SET HEAD off;
5 set trimspool on;
6 column xxx format a400
7 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
8 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
9 --exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', true);
10 --exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', true);
11 exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true);
12 SELECT DBMS_METADATA.GET_DDL('TABLE',D.TABLE_NAME)||';' FROM user_tables D where table_name='EMP';
13 SELECT DBMS_METADATA.GET_DEPENDENT_DDL ('REF_CONSTRAINT','EMP','SCOTT') FROM DUAL;
14 set head on;
15* set feed on;
|
|
|
Re: Create a table from another table with all constraints [message #155010 is a reply to message #154832] |
Wed, 11 January 2006 13:25 |
Karthik143
Messages: 4 Registered: January 2006
|
Junior Member |
|
|
Thanks friends for all your input. I'll explain why we have to do this.
First of all, not all the constraints are editable by the user. The user can just modify the data type. Basically the user means here the administrator of the application. The customers should be able to create their own tables to have a proper data mart for them. That's y the option is given.
Using this input the application creates the tables in their data mart. So when the user feels that some of the field data type needs to be changed or a new field needs to be added, he will change that in the application and then the appl. will have to generate/modify the table definition. This is the brief idea behind what we are doing.
We can as well regenerate the whole table. But the user might want to have the data's stored. So we need to adapt the new changes without loosing the data.
The problems I face here is that altering table is not possible when the column is not empty. But If I do a casting then the data length is not shown and the not null able is getting changed to null.
So I wanted to know if there is any other way of altering the table with the new data type.
Hope now this is clear. Advance thanks for your inputs.
|
|
|
Re: Create a table from another table with all constraints [message #155175 is a reply to message #155010] |
Thu, 12 January 2006 13:54 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you just want the administrator to be able to change the datatype of a column without losing the data or the not null constraint, then you can temporarily create a new column, update the data from the original column to the new column, disable the constraints on the original column, modify the original column, then update the data from the new column to the original column, enable the constraints, and drop the temporary column. Please see the demonstration below.
-- if you have this:
scott@ORA92> DESC ProductUpd_A
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
SALES_PRICE NOT NULL VARCHAR2(10)
scott@ORA92> SELECT * FROM ProductUpd_A
2 /
SALES_PRIC
----------
3.95
-- you cannot do this:
scott@ORA92> ALTER TABLE ProductUpd_A
2 MODIFY (sales_price NUMBER (14, 2))
3 /
MODIFY (sales_price NUMBER (14, 2))
*
ERROR at line 2:
ORA-01439: column to be modified must be empty to change datatype
-- but you can do this:
scott@ORA92> ALTER TABLE ProductUpd_A
2 ADD (new_sales_price NUMBER (14,2))
3 /
Table altered.
scott@ORA92> UPDATE ProductUpd_A
2 SET new_sales_price = sales_price
3 /
1 row updated.
scott@ORA92> BEGIN
2 FOR r IN
3 (SELECT DISTINCT constraint_name
4 FROM user_cons_columns
5 WHERE table_name = 'PRODUCTUPD_A'
6 AND column_name = 'SALES_PRICE')
7 LOOP
8 EXECUTE IMMEDIATE 'ALTER TABLE ProductUpd_A DISABLE CONSTRAINT '
9 || r.constraint_name;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
scott@ORA92> UPDATE ProductUpd_A
2 SET sales_price = NULL
3 /
1 row updated.
scott@ORA92> ALTER TABLE ProductUpd_A
2 MODIFY (sales_price NUMBER (14, 2))
3 /
Table altered.
scott@ORA92> UPDATE ProductUpd_A
2 SET sales_price = new_sales_price
3 /
1 row updated.
scott@ORA92> BEGIN
2 FOR r IN
3 (SELECT DISTINCT constraint_name
4 FROM user_cons_columns
5 WHERE table_name = 'PRODUCTUPD_A'
6 AND column_name = 'SALES_PRICE')
7 LOOP
8 EXECUTE IMMEDIATE 'ALTER TABLE ProductUpd_A ENABLE CONSTRAINT '
9 || r.constraint_name;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
scott@ORA92> ALTER TABLE ProductUpd_A
2 DROP COLUMN new_sales_price
3 /
Table altered.
-- results:
scott@ORA92> DESC ProductUpd_A
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
SALES_PRICE NOT NULL NUMBER(14,2)
scott@ORA92> SELECT * FROM ProductUpd_A
2 /
SALES_PRICE
-----------
3.95
scott@ORA92>
|
|
|
|
Re: Create a table from another table with all constraints [message #199067 is a reply to message #155300] |
Fri, 20 October 2006 00:10 |
Karthik143
Messages: 4 Registered: January 2006
|
Junior Member |
|
|
Hi,
I have a question with respect to performance. Since this will be a datamart. It is quite possible that it will have millions of records. In this case which one will be better Alter or CTAS?
I can implement the changes based on the impack on performance. Please help me in understanding this.
Thanks & Regards,
Karthik.
|
|
|
Re: Create a table from another table with all constraints [message #199112 is a reply to message #199067] |
Fri, 20 October 2006 04:43 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
>> It is quite possible that it will have millions of records. In this case which one will be better Alter or CTAS?
Depends on what you are doing with the ALTER.
If the new column has default value or if you are ending up updating every row in table, ALTER may be painful and may also lead to row migration.
In that case, create a new table with all required columns and specification and do an insert into new_table (select * from old table) with APPEND hint.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 23:33:14 CDT 2024
|