Populate tables with a circular reference

Last modified: October 8, 2024

A circular reference is a series of references where the last object references the first, resulting in a closed loop.

There are variations of circular references in databases, and Data Generator deals with them differently.

Example 1. Circular reference within one table

CREATE TABLE dbo.references_in_table (
ID int NOT NULL,
clm_ref1 int NOT NULL UNIQUE,
clm_ref2 int NOT NULL UNIQUE,
PRIMARY KEY CLUSTERED (ID)
)
ON [PRIMARY]
GO

ALTER TABLE dbo.references_in_table WITH NOCHECK
ADD FOREIGN KEY (clm_ref1) REFERENCES dbo.references_in_table (clm_ref2)
GO

ALTER TABLE dbo.references_in_table WITH NOCHECK
ADD FOREIGN KEY (clm_ref2) REFERENCES dbo.references_in_table (clm_ref1)
GO

When trying to populate those tables, the following error will be displayed:

The data generation process for the foreign key is not possible. The table contains a foreign key that refers to another foreign key within the same table. This version of data generator can not resolve this issue.

In such cases, data generation is not possible.

Example 2. Two tables reference each other

CREATE TABLE dbo.reference_in_table1 (
ID int NOT NULL,
clm_ref1 int NOT NULL UNIQUE,
PRIMARY KEY CLUSTERED (ID)
)
ON [PRIMARY]
GO

CREATE TABLE dbo.reference_in_table2 (
ID int NOT NULL,
clm_ref1 int NOT NULL UNIQUE,
PRIMARY KEY CLUSTERED (ID)
)
ON [PRIMARY]
GO

ALTER TABLE dbo.reference_in_table1 WITH NOCHECK
ADD FOREIGN KEY (clm_ref1) REFERENCES dbo.reference_in_table2 (clm_ref1)
GO

ALTER TABLE dbo.reference_in_table2 WITH NOCHECK
ADD FOREIGN KEY (clm_ref1) REFERENCES dbo.reference_in_table1 (clm_ref1)
GO

When trying to populate such tables, the following error will be displayed:

Column X. It is not possible to generate data with a selected generator due to a circular dependency. Please select another generator.

In these situations, we recommend changing the generator for any other valid one.