Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
Short answer: circular foreign keys are not automatically invalid, but two mutually mandatory foreign keys create a dependency loop that is difficult to insert, update, delete, migrate, and cascade safely. In most designs, keep ownership in one direction and model a preferred or special child with a nullable foreign key, an association table, or a separate role column.
This is the enduring lesson of Michelle A. Poolet’s June 30, 1999 article, “SQL By Design: The Circular Reference”. Its example remains useful, but its SQL Server 6.5 and 7.0 context should not be treated as a universal description of modern database behavior.
What is a circular foreign-key reference?
A circular reference exists when the foreign-key dependency graph contains a cycle:
Recommended Free Tools
Table A ──> Table B ──> Table AThe cycle can involve more tables:
A ──> B ──> C ──> AFor example, if Customer.billing_site_id references CustLocation.site_id, while CustLocation.customer_id references Customer.customer_id, each table depends on the other.
#1 Best Overall
- Used Book in Good Condition
The practical problem is most severe when both foreign keys are NOT NULL, checked immediately, and required for every row. There is then no valid first insert: the customer requires a location, but the location requires the customer.
This is different from several related concepts:
- Self-reference: a table references itself, as in an employee hierarchy. SQL Server explicitly supports self-referencing foreign keys.
- Recursive data: an organization chart, folder tree, or bill of materials can contain recursive relationships without creating a schema-level dependency cycle.
- Graph cycles: application data may legitimately form a graph cycle. That does not mean the tables themselves have mutually mandatory foreign keys.
- View or query recursion: circular view, procedure, or query dependencies are separate problems.
Thus, “cycle” does not automatically mean “bad.” The key question is whether the database can enforce the intended lifecycle and business rules clearly.
The Customer–Location–Contact example
Poolet’s article uses three conceptual tables:
Customer CustLocation CustContact
-------- ------------ -----------
CustNo SiteNo ContactNo
CompanyName CustNo SiteNo
BillingSiteNo PrimaryContactNo The intended business relationships are reasonable:
- A customer has one or more locations.
- Each location belongs to a customer.
- A customer may designate one location as its billing location.
- A location may designate one contact as its primary contact.
- A contact works from a location.
The circularity comes from representing both the ordinary ownership relationship and the special selection in opposite directions:
Customer.BillingSiteNo ──> CustLocation.SiteNo
CustLocation.CustNo ──> Customer.CustNo
CustLocation.PrimaryContactNo ──> CustContact.ContactNo
CustContact.SiteNo ──> CustLocation.SiteNoThe ordinary ownership direction is straightforward: a location belongs to a customer, and a contact belongs to a location. The reverse links are preferences or selections. Treating those selections as mandatory reverse foreign keys creates the cycle.
Why the first insert fails
Consider the simplified schema:
CREATE TABLE Customer (
customer_id INTEGER PRIMARY KEY,
billing_site_id INTEGER NOT NULL
);
CREATE TABLE CustLocation (
site_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL
);The exact syntax and whether a particular database accepts the complete definition vary by product and version. Conceptually, the two constraints are:
Customer.billing_site_id ──> CustLocation.site_id
CustLocation.customer_id ──> Customer.customer_idInserting the customer first fails because site 100 does not yet exist:
INSERT INTO Customer (customer_id, company_name, billing_site_id)
VALUES (1, 'Acme', 100);Inserting the location first fails because customer 1 does not yet exist:
INSERT INTO CustLocation (site_id, customer_id)
VALUES (100, 1);There is no valid first statement when both references are mandatory and enforced immediately. The same chicken-and-egg problem occurs between a location and its primary contact.
Rank #2
The problem is not limited to INSERT
UPDATE operations
A common workaround is to create one row with a temporary NULL, placeholder, or disabled constraint, create the other row, and then fill in the reference. That can work only if the schema allows the intermediate state and all steps occur reliably in one transaction.
If the application fails after the first statement, the database may be left with an incomplete relationship. A placeholder can be worse: it may look like a real relationship and contaminate reports or downstream processing.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11DELETE operations
Deleting either side can violate the other side’s foreign key. For example, deleting a location that is selected as a customer’s billing site leaves a dangling reference unless the operation first changes the customer or uses an appropriate referential action.
Automatic cascading deletion does not make the model automatically safe. A cascade can become cyclic or can reach the same table through multiple paths.
Bulk loading
Ordinary parent-child data has a natural load order: insert parents, then children. A circular dependency has no such topological order. Imports therefore need staged loading, deferred checks, temporary nulls, or a redesigned schema.
Schema migrations
Adding a new mandatory foreign key to populated tables commonly requires a staged migration:
Free tools Windows power users keep installed
One-click scans. No signup required.
- Add the new column as nullable.
- Backfill valid references.
- Check for missing, invalid, or cross-owner references.
- Add indexes and the foreign-key constraint.
- Make the column
NOT NULLonly after every row satisfies the rule. - Remove obsolete columns and constraints after dependent code has been migrated.
Trying to add both sides as immediately mandatory constraints can reproduce the same circular dependency during deployment.
The original article’s redesign
The cleanest general model is:
Customer 1 ───< CustLocation 1 ───< CustContactKeep only the ownership foreign keys and represent special roles within the dependent tables:
CREATE TABLE Customer (
customer_id INTEGER PRIMARY KEY,
company_name VARCHAR(200) NOT NULL
);
CREATE TABLE CustLocation (
site_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
address_type CHAR(1) NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES Customer(customer_id),
CHECK (address_type IN ('B', 'O'))
);
CREATE TABLE CustContact (
contact_id INTEGER PRIMARY KEY,
site_id INTEGER NOT NULL,
contact_type CHAR(1) NOT NULL,
FOREIGN KEY (site_id)
REFERENCES CustLocation(site_id),
CHECK (contact_type IN ('P', 'S'))
);Here, B can mean billing and O other; P can mean primary and S secondary. The exact values are a design choice. The important change is that every structural dependency points in one direction.
The normal insertion sequence is then:
INSERT INTO Customer (customer_id, company_name)
VALUES (1, 'Acme');
INSERT INTO CustLocation (site_id, customer_id, address_type)
VALUES (100, 1, 'B');
INSERT INTO CustContact (contact_id, site_id, contact_type)
VALUES (500, 100, 'P');This design is easy to load, delete, migrate, and understand. It also matches the likely semantics: a location belongs to a customer; “billing” is a role assigned to that location.
However, a type column alone does not guarantee exactly one billing location or exactly one primary contact. That requires an additional uniqueness rule or transactional enforcement.
Modern alternatives
1. Make the selected-child link nullable
If a customer can exist before a billing location is chosen, model that lifecycle explicitly:
Customer.billing_site_id NULLThen create the customer, create its location, and complete the association:
INSERT INTO Customer (customer_id, company_name, billing_site_id)
VALUES (1, 'Acme', NULL);
INSERT INTO CustLocation (site_id, customer_id, address_type)
VALUES (100, 1, 'B');
UPDATE Customer
SET billing_site_id = 100
WHERE customer_id = 1;A nullable foreign key is not automatically a design flaw. It can accurately represent “not selected yet.” Document whether NULL means not yet assigned, not applicable, or unknown; those meanings should not be silently mixed.
A simple foreign key on billing_site_id may still allow customer 1 to select a location owned by customer 2. To prevent that, use a composite key and composite foreign key:
-- Conceptual relationship
FOREIGN KEY (customer_id, billing_site_id)
REFERENCES CustLocation(customer_id, site_id)The referenced columns must have a matching primary-key or unique constraint, and the exact DDL varies by DBMS.
2. Use an association table
When the special relationship has its own meaning, an association table is often the strongest model:
CustomerBillingSite
-------------------
customer_id
site_idPossible constraints include:
PRIMARY KEY (customer_id)
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
FOREIGN KEY (customer_id, site_id)
REFERENCES CustLocation(customer_id, site_id)This says that a customer can have at most one current billing site while ensuring that the selected site belongs to that customer. The table can later hold effective dates, approval status, audit columns, or the user who made the selection.
Rank #4
The same pattern works for primary contacts, preferred payment methods, account managers, default images, and other cases where a parent selects one member from a collection.
3. Use role tables when roles can multiply
A single address_type column is restrictive if one location may be both billing and shipping, or if new roles will be added. A role association table is more flexible:
CustomerLocationRole
--------------------
customer_id
site_id
role_codeA primary key such as (customer_id, site_id, role_code) prevents duplicate role assignments. Additional unique or filtered rules can enforce one selected location for a particular role where the database supports them.
4. Use deferred constraints when the mutual dependency is intentional
Some database systems support foreign keys that are checked at transaction commit rather than after each statement. PostgreSQL documentation describes DEFERRABLE constraints and SET CONSTRAINTS ... DEFERRED. In a supported configuration, mutually dependent rows can be created in one transaction as long as the final committed state satisfies both constraints.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →BEGIN;
INSERT INTO customer (customer_id, billing_site_id)
VALUES (1, 100);
INSERT INTO cust_location (site_id, customer_id)
VALUES (100, 1);
COMMIT;This is illustrative PostgreSQL-style behavior, not portable SQL and not a general SQL Server solution. Deferral solves statement ordering; it does not solve cross-owner references, “exactly one” rules, deletion policy, or unclear business semantics.
5. Use procedures or triggers selectively
Triggers can enforce cross-table rules that ordinary foreign keys cannot express, but they add hidden write behavior, ordering concerns, recursion risks, locking complexity, and testing overhead. A stored procedure or service-layer command is often clearer when the rule represents a business workflow rather than basic referential integrity.
Keep ordinary foreign keys in place wherever possible. Flexibility is not a reason to replace simple declarative constraints with procedural logic.
SQL Server behavior: an important qualification
SQL Server supports foreign keys, self-referencing foreign keys, and referential actions including NO ACTION, CASCADE, SET NULL, and SET DEFAULT, subject to restrictions. A foreign-key value that is not NULL must match a value in the referenced primary or unique key.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesSQL Server does not reject every pair of mutually referencing foreign keys under every configuration. Its documented error 1785 concerns cascading referential-action trees that contain a cycle or more than one path to the same table. See the SQL Server documentation for error 1785.
Therefore, do not simplify the rule to “SQL Server does not allow circular foreign keys.” The more accurate statement is that SQL Server restricts certain cascading paths, while mandatory mutual references can still be operationally awkward even when the constraints themselves can be created.
For SQL Server’s current foreign-key behavior and self-reference support, consult Create foreign key relationships and Primary and foreign key constraints.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Enforcing the rules the foreign keys do not express
Exactly one billing location
If billing status is stored on locations, a normal check constraint usually cannot enforce “one and only one per customer.” Where supported, a filtered or partial unique index can enforce the upper bound:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
CREATE UNIQUE INDEX one_billing_location_per_customer
ON dbo.CustLocation(customer_id)
WHERE address_type = 'B';PostgreSQL uses the equivalent partial-index idea:
CREATE UNIQUE INDEX one_billing_location_per_customer
ON cust_location (customer_id)
WHERE address_type = 'B';This ensures no more than one billing location. If the business rule requires at least one, enforce that at the correct workflow boundary—for example, when activating a customer—rather than necessarily making initial customer creation impossible.
Exactly one primary contact
The same principle applies to primary contacts. A uniqueness rule can prevent two primary contacts for the same location, but a separate workflow rule may be needed to require one before the location becomes active.
Ownership consistency
Always ask whether a selected child must belong to the same parent that selected it. A foreign key referencing only site_id may not enforce that. Composite keys and composite foreign keys are the declarative solution when ownership is part of the invariant.
Deletes, cascades, and key updates
Prefer one clear cascade direction. For the customer model, deleting a customer might deliberately delete its locations and contacts, but deleting a location should usually not silently delete the customer. Depending on the application, use:
NO ACTIONwith an explicit deletion order;- a one-way
CASCADE; SET NULLfor an optional selection;- soft deletion; or
- an archival process.
SET NULL requires a nullable foreign-key column. Cascading updates to primary keys are usually avoidable; stable identifiers reduce the need for ON UPDATE CASCADE and limit the amount of dependency propagation.
If a circular schema already exists
Do not begin by disabling every constraint and hoping the migration ends cleanly. A safer transition is:
- Map the dependency graph and identify the ownership direction.
- Find invalid, missing, and cross-owner references.
- Add a nullable replacement column or association table.
- Backfill it in batches, with validation queries and a rollback plan.
- Update application writes to use the new one-way model.
- Enforce uniqueness, composite ownership, and role rules.
- Remove or deprecate the reverse foreign key after dependent code is gone.
- Make columns mandatory only when the business lifecycle truly requires it.
If constraints must be disabled for a controlled migration, explicitly validate the data before re-enabling them. In SQL Server, sys.foreign_keys.is_not_trusted exposes whether a foreign key is trusted. An enabled but untrusted constraint is not equivalent to a fully validated constraint for all optimizer and integrity purposes. See the SQL Server foreign-key metadata documentation.
A practical design checklist
- Which relationship is ownership? Put that foreign key in the dependent table.
- Can either row exist independently? If yes, do not make both references mandatory.
- Is the reverse link structural or merely a preference? Preferences usually belong in a nullable link or association table.
- Must the selected child belong to the same parent? Use a composite key and foreign key when necessary.
- Does “exactly one” matter? Add a unique or filtered index for the upper bound and a workflow rule for the lower bound.
- What happens on delete? Choose and document
NO ACTION, one-way cascade, nullification, soft deletion, or archival. - Does the target DBMS support deferred constraints? Verify the exact product and version before relying on them.
- Will the relationship gain dates, approvals, or audit data? Start with an association table.
- Can the schema be loaded in a clear order? If not, redesign or deliberately use a supported transactional technique.
Conclusion
The 1999 article’s “chicken-and-egg” warning is still sound: two mutually mandatory, immediately enforced foreign keys make a schema harder to populate and maintain. But circular references are not automatically forbidden, unnormalized, or impossible. Some engines support carefully deferred constraints, and some non-cascading mutual references may be technically creatable.
Recommended Free Tools
The better modern rule is precise: use one-way foreign keys for ownership; use nullable links or association tables for a selected, preferred, or special child; enforce cross-owner and one-per-parent rules explicitly; and use deferred constraints only when the mutual dependency is intentional and supported by the target DBMS.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
