Managing Schema Dependencies
Getting deployment order right when tables, views, and procedures depend on each other
By the SchemaSmith Team ยท Last reviewed
Getting deployment order right when tables, views, and procedures depend on each other
By the SchemaSmith Team ยท Last reviewed
Database objects depend on each other: foreign keys reference other tables, views reference tables and other views, stored procedures call other procedures. Deploying schema changes in the wrong order causes failures. Manage these dependencies by understanding the dependency graph, deploying in topological order, and handling circular dependencies when they arise. Most schema management tools resolve table-level ordering automatically, but cross-database references and complex procedure chains often need explicit configuration.
Database objects rarely exist in isolation. Understanding the types of dependencies in your schema is the first step toward reliable deployments.
Table A's foreign key points to Table B, meaning Table B must exist before Table A's constraint can be created. This is the most common and most easily automated dependency type.
A view references one or more tables or other views in its definition. If those underlying objects do not exist or have changed structure, the view creation will fail or return incorrect results.
A procedure may call other procedures, reference tables, or use functions. Some databases allow deferred resolution where the referenced object does not need to exist at creation time. Others enforce strict ordering.
Functions used in computed columns, check constraints, or other functions create hard dependencies. The function must exist before the table or constraint that references it can be created.
Objects in one database that reference objects in another introduce deployment-order dependencies that span database boundaries, making them harder to manage with single-database tooling.
Synonyms that point to objects in other databases or servers act as indirection layers. The synonym itself can be created without the target existing, but anything that uses the synonym will fail at runtime if the target is missing.
Every database has an implicit dependency graph that describes which objects must exist before others can be created. Every foreign key, every view, every procedure that calls another procedure is a directed edge in this graph. Understanding the graph is essential for reliable deployments.
Tables with no foreign keys can be created in any order. Tables with foreign keys must be created after their referenced tables. If Orders has a foreign key to Customers, then Customers must be deployed first. The constraint is absolute: the database engine enforces it whether you track it or not.
The deployment tool needs to resolve this graph into a linear order so that every dependency appears before the objects that depend on it. This process is called topological sorting. For most databases, the table dependency graph is a DAG (directed acyclic graph), and standard algorithms handle thousands of tables in milliseconds.
A DAG means no cycles, no circular references. Problems arise when the graph has cycles or when dependencies cross database boundaries. Both scenarios require strategies beyond simple topological sorting: circular dependencies need deferred constraints or multi-pass deployment; cross-database references need explicit ordering at the package level.
Most teams never have to think about the graph directly because the tooling resolves it automatically. The cases that do require attention, circular references and cross-database references, are the ones the rest of this guide focuses on.
Circular dependencies occur when Table A references Table B and Table B references Table A, either directly or through a chain of intermediate tables. Breaking the cycle requires choosing a strategy that fits your schema and deployment constraints.
Create all tables first without foreign key constraints, then add constraints after every table exists. SQL Server supports this pattern using WITH NOCHECK followed by CHECK to validate existing data after the fact.
Split deployment into phases: the first pass creates all tables and columns, the second pass adds all foreign key constraints. This guarantees that every referenced table exists before any constraint is enforced.
Sometimes circular foreign keys indicate a design problem. Consider whether one of the references should be an application-level relationship instead of a database constraint, or whether the tables should be restructured.
Make one side of the circular reference nullable, allowing tables to be created in either order. Insert the row first with a NULL reference, then update it after the referenced row exists in the other table.
When objects in one database reference objects in another, dependency management extends beyond a single deployment target. These strategies cover ordering and abstracting cross-database references.
If Database B depends on Database A, then A must be deployed first. This ordering needs to be defined explicitly, since most tools only resolve dependencies within a single database.
Use synonyms to decouple cross-database references. Instead of hardcoding a three-part name, point a synonym at the target object. The synonym can be updated independently without changing the referencing code.
References like [Database].[Schema].[Object] are fragile because they break when database names differ across environments. A view referencing ProductionDB.dbo.Customers will fail in a dev environment where the database is named DevDB.
Cross-database references often need different target names per environment. Deployment tooling should support token substitution or configuration files that map logical database names to physical names for each environment.
Reliable deployment ordering combines automated dependency resolution with explicit configuration for the cases automation cannot handle.
Most schema management tools resolve table foreign key dependencies automatically. Trust the tool for this and focus your effort on the cases it cannot resolve, such as cross-database references and circular dependencies.
When managing multiple databases, specify which database deploys first. Do not rely on alphabetical order or convention. Make the deployment sequence a first-class configuration value.
Deploy all schema objects first, then load reference or seed data. Mixing schema changes and data operations in the same deployment step creates ordering complexity that is difficult to debug.
Deploy from scratch regularly to catch ordering issues that do not surface in incremental deployments. An incremental deployment may succeed simply because the required objects already exist from a previous run.
If anything requires manual ordering that the tool cannot resolve, document it clearly rather than relying on tribal knowledge. The next person deploying should not need to guess the correct sequence.
Deployment order configuration should live in source control alongside the schema definitions. Treat it as code: review changes, track history, and ensure every environment uses the same configuration.
A schema package is your source of truth for what your databases should look like. At the top level, Product.json defines a collection of Templates with explicit deployment ordering via the TemplateOrder array. This gives direct control over which databases deploy first when cross-database dependencies exist.
Within each Template, SchemaQuench resolves table dependencies automatically based on foreign key references. It connects to each target database and creates and alters tables in the correct order without manual configuration. The same engine handles SQL Server, PostgreSQL, and MySQL.
Cross-database dependencies are managed by ordering Templates within the Product definition. If Database B references objects in Database A, position A's Template before B's in the TemplateOrder array. SchemaQuench processes Templates in that sequence, so A is in place before B attempts its cross-database references.
Migration scripts run in defined slots for cases that need explicit ordering beyond what the tool calculates. Scripts can run before or after table changes, before or after data delivery, or in custom slots defined under each Template's script folders. This covers scenarios like circular FK setup or any imperative one-off operation that requires specific sequencing.
Script tokens defined under ScriptTokens in Product.json handle environment-specific database names in cross-database references. Define a token like {{RegistryDb}} with a default value of Registry, then override it per environment via the SmithySettings_ScriptTokens__RegistryDb environment variable. The same schema package works across dev, staging, and production without hardcoded database names.