SQL Data Generator 2

Self-referencing table constraints can cause generation to stop

When generating data against a table which has foreign key references to itself, the following error may occur:

Generation stopped. The generator for column [COLUMN] could not generate any more values

The following example illustrates a reproduction for this issue:

CREATE TABLE [dbo].[Account]
(
[AccountID] [uniqueidentifier] NOT NULL,
[AccountNumber] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AccountType] [int] NOT NULL,
[LedgerID] [uniqueidentifier] NULL,
[UniqueID] [int] NOT NULL IDENTITY(1, 1),
[Version] [datetime] NOT NULL,
[FinYearID] [uniqueidentifier] NOT NULL
)

GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [AccountTypeRule] CHECK (([AccountType]=(1) OR [AccountType]=(2) OR [AccountType]=(3) OR [AccountType]=(4) OR [AccountType]=(5)))
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ([UniqueID])
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [UK_AccountID_FinYearID] UNIQUE NONCLUSTERED ([AccountID], [FinYearID])
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [UK_AccountName_FinYearID] UNIQUE NONCLUSTERED ([AccountName], [FinYearID])
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [FK_Account_FinancialYear] FOREIGN KEY ([FinYearID]) REFERENCES [dbo].[FinancialYear] ([FinYearID])
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [FK_LedgerID_AccountID] FOREIGN KEY ([LedgerID], [FinYearID]) REFERENCES [dbo].[Account] ([AccountID], [FinYearID])
GO

This is a known issue and we are currently evaluating support for this scenario.


Didn't find what you were looking for?