Conforming to unique constraints in SQL Data Generator

Category: Troubleshooting & error messages
Date: 26 Nov 2008
Product: SQL Data Generator
Tables that have columns with unique constraints and indexes may require special attention when generating data using SQL Data Generator.

For example, the following table contains a unique index on the TicketTypeCode column. SQL Data Generator may generate non-unique and NULL data for this column by default, violating the constraint:

CREATE TABLE [dbo].[TicketType](
[TicketTypeID] [int] IDENTITY(1,1) NOT NULL,
[TicketTypeCode] [nvarchar](10) NOT NULL,
[TicketTypeName] [nvarchar](50) NOT NULL,
[RowVersion] [timestamp] NOT NULL,

CONSTRAINT [PK_TicketType] PRIMARY KEY CLUSTERED
(
[TicketTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_TicketType] ON [dbo].[TicketType]
(
[TicketTypeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

In order for data in the above example to be generated, change the setting for the column [TicketTypeCode] in SQL Data Generator to an Account Number or 5 Digit ID, and make sure the "Set unique" option is selected. This will allow the data to be generated.