Page 1 of 1

Generate tables in DDL in sequence

PostPosted: Mon Oct 23, 2017 3:18 am
by andrew.mcgimpsey
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.

Re: Generate tables in DDL in sequence

PostPosted: Tue Oct 24, 2017 6:46 am
by gedkvi
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