Populate a table referencing another table that already contains data

Last modified: October 8, 2024

When trying to populate a table with circular reference and not including a table it references to data generation, the following error will be displayed:

Column refers to X column of Y table which is not included in the data generation.

The following example illustrates tables with circular reference:

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

This happens when you don’t want to populate the second table as it already contains data and don’t include it in data generation.

To solve this issue, we recommend changing the Foreign Key (auto assigned) generator for the Table or View generator.