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: 48
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


Return to Cameo Data Modeler

Who is online

Users browsing this forum: No registered users and 0 guests

cron