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 Go to next message
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 Go to previous messageGo to next message
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 #27678 is a reply to message #27675] Wed, 22 October 2003 09:06 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
and ofcourse,you will have to change the constraint names ..
Re: Create a table from another table with all constraints [message #27700 is a reply to message #27675] Fri, 24 October 2003 03:29 Go to previous messageGo to next message
Venugopalacharyulu kada
Messages: 13
Registered: February 2003
Junior Member
Thanks for sending,

If I pased the following scipt it is showing error at "get_ddl".

select dbms_metadata.get_ddl('TABLE','T') from dual;

I can't under stand meanings for 'TABLE' and 'T'.
Re: Create a table from another table with all constraints [message #27723 is a reply to message #27700] Fri, 24 October 2003 14:38 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
I believe I answered your question in another thread.
Re: Create a table from another table with all constraints [message #27754 is a reply to message #27723] Mon, 27 October 2003 20:54 Go to previous messageGo to next message
Venugopalacharyulu kada
Messages: 13
Registered: February 2003
Junior Member
But it is giving the error like

ORA-00904: invalid column name

One more thing here i am using oracle 8.1.6.
Please kindly note this version.
Re: Create a table from another table with all constraints [message #154631 is a reply to message #27675] Mon, 09 January 2006 19:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #154832 is a reply to message #154631] Tue, 10 January 2006 20:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You cannot specify constraints with the "create table ... as select ..." syntax. If you need to keep that syntax, as indicated in your private message, then you will need to add the constraints afterwards. It is unusual to have users creating tables. Perhaps if you describe the whole problem more completely, an alternative can be provided that does not involve having users create tables or require the duplication of constraints. You could write some code to loop through the user_constraints and user_cons_columns data dictionaries and dynamically add constraints, but it would be cumbersome. Do you need all of the constraints, primary and foreign keys, check constraints, and not null constraints?





Re: Create a table from another table with all constraints [message #154882 is a reply to message #154832] Wed, 11 January 2006 02:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #155300 is a reply to message #155175] Fri, 13 January 2006 11:55 Go to previous messageGo to next message
Karthik143
Messages: 4
Registered: January 2006
Junior Member
Thanks Barbara. I'll try this out.
Re: Create a table from another table with all constraints [message #199067 is a reply to message #155300] Fri, 20 October 2006 00:10 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Sending an mail attachment thru Oracle8i database
Next Topic: About Boolean Function
Goto Forum:
  


Current Time: Thu Mar 28 23:33:14 CDT 2024