Skip to content
Advertisement

How to order queries execution in SQL script so that they are executed in the right order?

Let’s say that I have three tables:

Table References
A
B A
C B

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...)

Advertisement

Answer

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.

10 People found this is helpful
Advertisement