Let’s say that I have three tables:
and I have created a SQL DDL script with these tables’ definitions:
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?
CREATE TABLE B(...REFERENCES A...) CREATE TABLE A(...) CREATE TABLE C(...REFERENCES B...)
CREATE TABLE A(...) CREATE TABLE B(...REFERENCES A...) CREATE TABLE C(...REFERENCES B...)
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.