Let's say that I have three tables:

and I have created a SQL DDL script with these tables' definitions:

script.sql:

CREATE TABLE C(...REFERENCES B...)
CREATE TABLE A(...)
CREATE TABLE B(...REFERENCES A...) 

IMPORTANT: The order of definitions in the script can be random!

Is there are way/tool that would take the script as input and return another SQL script with sorted DDL queries?

Example:

Input scriptInput.sql:

CREATE TABLE B(...REFERENCES A...)   
CREATE TABLE A(...)
CREATE TABLE C(...REFERENCES B...)

Output - scriptOutput.sql:

CREATE TABLE A(...)
CREATE TABLE B(...REFERENCES A...) 
CREATE TABLE C(...REFERENCES B...)

CodePudding user response：

The typical solution is to decouple the table creation from the constraint creation. For example, your script could look like:

CREATE TABLE C(...);
CREATE TABLE A(...);
CREATE TABLE B(...);

alter table c add constraint fk1 foreign key (x) references b (y);
alter table b add constraint fk2 foreign key (z) references a (w);

This is how production-grade scripts are typically created. This is fool proof since a different person (role DBA) will execute the script in production, and will execute the script without trying to improve it. In case of success (or error) the DBA will provide the execution log back to you.

CodePudding user response：

There is a solution that use the super instruction CREATE SCHEMA. Of course you need to use a specific schema to do that. Not the ugly dbo schema. Example :

CREATE SCHEMA SCH_TEST
CREATE TABLE B(PK_B INT PRIMARY KEY, FK_A INT REFERENCES A (PK_A))   
CREATE TABLE A(PK_A INT PRIMARY KEY)
CREATE TABLE C(PK_C INT PRIMARY KEY, FK_B INT REFERENCES B (PK_B))
;

That's all !

It comes from Standard ISO SQL language...