When you’re building or maintaining a relational database, objects rarely live in isolation. Tables support views, views feed reports, procedures call other procedures, and constraints tie data together. These relationships are called dependencies, and they can be hard or soft.
The difference boils down to how strictly the database enforces the relationship.
Hard Dependency
A hard dependency (also referred to as a structural dependency) is one the database engine enforces at all times. If the supporting object doesn’t exist, the dependent object cannot be created or kept valid. The engine prevents you from dropping or altering objects in ways that would break the dependency.
Examples of hard dependencies:
- Foreign keys: A child table cannot exist without its referenced parent.
- Primary key/unique constraints: Other objects relying on these constraints block their removal.
- Check constraints and default constraints: If they call a function, that function must exist at creation and remain valid.
- Schema-bound views: These are views that are enforced with
WITH SCHEMABINDING. When a view is schema-bound, you can’t drop or alter the underlying table/column without first dropping the view. - Indexed views or persisted computed columns: Require the underlying structures to remain valid.
- Stored procedures and functions with direct references: At creation time, the objects they reference must exist (though after creation, enforcement weakens – see soft dependencies below).
- Views with direct references: Must resolve underlying tables and columns at creation time.
With hard dependencies, you get safety and clarity, but they demand strict deployment ordering and make schema changes more rigid.
Soft Dependency
A soft dependency is looser. The database allows the dependent object to exist even if the supporting object doesn’t, or it doesn’t enforce ongoing validity. Errors surface only at runtime (when the object is used), not at creation.
Examples of soft dependencies:
- Views: While they check object existence at creation, they do not enforce ongoing validity. If a referenced column is dropped later, the view compiles but fails when queried.
- Stored procedures and functions with direct references: Same story. These must resolve at creation, but they don’t block you from later dropping a referenced object. They’ll just fail when executed.
- Stored procedures, triggers, or functions with dynamic SQL: Dependencies can’t be checked at compile time. The code creates fine, but fails at runtime if the referenced object doesn’t exist.
- Synonyms: The synonym can exist even if its target object doesn’t. The error only occurs when you use it.
- Linked servers or external sources: Objects can call them whether or not they’re currently available.
- Metadata-driven or late-bound calls: For example, using variables with
EXECto call another procedure.
Soft dependencies allow flexibility and late binding, but at the cost of predictability. You won’t always know something’s broken until you try to run it.
Why the Distinction Matters
While you don’t normally decide one over the other (for example, if you need a primary key you’ll be stuck with a hard dependency – you can’t choose to have a primary key as a soft-dependency), it’s useful to understand how each one impacts on your projects:
- Deployment: Hard dependencies force you to create objects in the right order. Soft dependencies give you wiggle room but can hide problems until runtime.
- Schema changes: Hard dependencies block you from dropping or altering objects in unsafe ways. Soft dependencies let you make changes, but may leave behind problems that only become apparent when code runs.
- Debugging: If an error happens immediately (compile or DDL time), you’re likely dealing with a hard dependency. If it surfaces only when executing code, it’ll be a soft dependency.
Quick Comparison
Here’s a quick recap that compares the two dependency types:
| Aspect | Hard Dependency | Soft Dependency |
|---|---|---|
| When checked | At creation and enforced ongoing | At creation only (sometimes) or deferred until runtime |
| Examples | Foreign keys, schema-bound views, constraints, indexed views, persisted computed columns | Views after creation, stored procs after creation, dynamic SQL, synonyms, linked servers |
| Error timing | Immediate | Deferred |
| Deployment impact | Strict ordering required | Flexible, but risk of hidden failures |
| Strengths | Safety, enforced integrity | Flexibility, dynamic design |
| Weaknesses | Brittle under schema changes | Harder to detect issues until runtime |