Handle Dependency While Generating Deployment Script

When generating deployment script for a db schema, I usually use delete-and-create approach shown in following code. The problem I often encounter is that it sometimes fails if I put a list of this type of code together, because of dependencies between the entities to be deleted and created.


IF OBJECT_ID('dbo.TE_DECOMP_CATEGORY') IS NOT NULL
DROP TABLE dbo.TE_DECOMP_CATEGORY
go

/* ***************************************************************
* @Summary: Create TE_DECOMP_CATEGORY table
* ***************************************************************/

CREATE TABLE dbo.TE_DECOMP_CATEGORY
(
CATEGORY varchar(30) NOT NULL,
DEFINITION varchar(200) NULL,

CONSTRAINT PK_TE_DECOMP_CATEGORY
PRIMARY KEY CLUSTERED (CATEGORY)
)

GRANT DELETE ON dbo.TE_DECOMP_CATEGORY TO DFR_application
go
GRANT INSERT ON dbo.TE_DECOMP_CATEGORY TO DFR_application
go
GRANT SELECT ON dbo.TE_DECOMP_CATEGORY TO DFR_application
go
GRANT UPDATE ON dbo.TE_DECOMP_CATEGORY TO DFR_application
go
GRANT SELECT ON dbo.TE_DECOMP_CATEGORY TO read_only
go
IF OBJECT_ID('dbo.TE_DECOMP_CATEGORY') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TE_DECOMP_CATEGORY >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TE_DECOMP_CATEGORY >>>'
go

Then I realized that I have to seperate the deletion logic from the creation one in order to avoid the problem. Theoretically it won't work no matter how I order them if I keep creation and deletion as a whole.


Imagine the following dependancies circular dependency is not allowed):

A->B->C->D;
B->E->F;
C->F;
E->D;

The deletion logic should be:

DELETE A;
DELETE B;
DELETE C;
DELETE E;
DELETE D;
DELETE F;

Then the reverse used for creation logic should be good enough.

To recap, to key idea for the resolving dependency issue with deployment script is to seperated deletion logic from creation logic. I believe most database products have dependency logic with generating script, what I need is to ensure the deletion logic follow the dependency order.