Generate tables in DDL in sequence

Discussions about database modeling product Cameo Data Modeler functionalities

Moderator: Moderators

Generate tables in DDL in sequence

Postby andrew.mcgimpsey » Mon Oct 23, 2017 3:18 am

Would anyone know if it is possible to get the generated DDL to contain the table structure in build sequence. At the minute the DDL being generated is like:

Table A (some columns)
Table B (some columns, FK to Table C)
Table C (some columns)

Obviously, when the DDL script is run, it will error as there is a dependency on Table C for Table B to be created. Ideally I would like the DDL to generated the tables in build sequence i.e.

Table A (some columns)
Table C (some columns)
Table B (some columns, FK to Table C)

Is there an option during the transformation stage or DDL generation that I am missing, or does this have to be a manual process after the DDL has been generated.

Thanks.
andrew.mcgimpsey
Forum Newbie
Forum Newbie
 
Posts: 5
Posts Rating:0
Joined: Wed Jul 26, 2017 6:39 am

Re: Generate tables in DDL in sequence

Postby gedkvi » Tue Oct 24, 2017 6:46 am

Hello,

There are a few nuances when generating DDLs.
The tool tries to build the table structure in a sequence ordered by foreign key dependencies, but:
There is a known issue regarding all DDL generators except Oracle flavor. These DDL generators can't handle cyclic dependencies. Oracle handles them by using ALTER statements, but not the others.
Looks like your model might contain cyclic dependencies.

Here is an example when there are no cyclic dependencies. Tables are sorted correctly:
TableFKs.mdzip


Code: Select all
--@(#) cycles.ddl

CREATE SCHEMA Schem;

CREATE TABLE Schem.C
(
   ID numeric PRIMARY KEY,
   fkA numeric
);

CREATE TABLE Schem.B
(
   ID numeric PRIMARY KEY,
   fkC numeric,
   FOREIGN KEY(fkC) REFERENCES Schem.C (ID)
);

CREATE TABLE Schem.A
(
   ID numeric PRIMARY KEY,
   fkB numeric,
   FOREIGN KEY(fkB) REFERENCES Schem.B (ID)
);


I have also attached a sample project of the script above.

Sincerely,
No Magic Customer Support
You do not have the required permissions to view the files attached to this post.
gedkvi
Customer Support
Customer Support
 
Posts: 53
Posts Rating:7
Joined: Wed Sep 16, 2015 11:25 am

Re: Generate tables in DDL in sequence

Postby renithharsan@gmail.com » Tue Dec 26, 2017 11:43 pm

DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.

• The create table statement does exactly that:

CREATE TABLE <table name> (
<attribute name 1> <data type 1>,
...
<attribute name n> <data type n>);
The data types that you will use most frequently are character strings, which might be called VARCHAR or CHAR for variable or fixed length strings; numeric types such as NUMBER or INTEGER, which will usually specify a precision; and DATE or related types. Data type syntax is variable from system to system; the only way to be sure is to consult the documentation for your own software.
more details about oracle
oracle training in bangalore :http://www.besanttechnologies.in/oracle-training-in-bangalore.html
oracle training in chennai : https://www.besanttechnologies.com/training-courses/oracle-training
renithharsan@gmail.com
Forum Newbie
Forum Newbie
 
Posts: 2
Posts Rating:0
Joined: Tue Dec 26, 2017 11:33 pm

Re: Generate tables in DDL in sequence

Postby firstenquiryseo1@gmail.com » Tue Jul 17, 2018 12:19 am

It’s hard to come by experienced people about this subject, but you seem like you know what you’re talking about! Thanks
Selenium Classes in Marathahalli Bangalore,Selenium Course in Marathahalli Bangalore,Automation Testing Course in Marathahalli Bangalore,Javascript Training in Marathahalli Bangalore
firstenquiryseo1@gmail.com
Forum Newbie
Forum Newbie
 
Posts: 2
Posts Rating:-1
Joined: Tue Jul 17, 2018 12:12 am

Re: Generate tables in DDL in sequence

Postby kingslydavid4592@gmail.com » Mon Aug 27, 2018 4:08 am

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
spool off;

Try this, and get some ideas from online. Thanks
Java Training in Chennai
kingslydavid4592@gmail.com
Forum Newbie
Forum Newbie
 
Posts: 1
Posts Rating:0
Joined: Mon Aug 27, 2018 3:50 am
Location: 164, 6th St, Thangal karai, Kumaran Colony, Vadapalani,Chennai, Tamil Nadu 600026
Full name: kingsly david


Return to Cameo Data Modeler

Who is online

Users browsing this forum: No registered users and 0 guests