| Author |
Message |
CHP
Joined: 27 Jul 2009
Posts: 5
|
 Posted: Mon Jul 27, 2009 7:40 pm Post subject: Foreign key generator with compound key |
 |
|
We're using SQL Data Generator 1.2. We have a table that is self-referential based on a compound key.
(In an accounting program, it's a parent-child relationship, they're accounts, and can have parent accounts within the same financial year. So, accounts have a parentID that is filled with another account's ID taken from accounts with the same FinancialYear).
When we try to generate accounts, it fails with the message: "Generation stopped. The generator for column ParentID could not generate any more values"
We've tried changing various settings and can't seem to get it to generate values for this field.
Any suggestions? |
|
| Back to top |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6347
Location: Red Gate Software
|
| Posted: Wed Jul 29, 2009 11:12 am Post subject: |
|
|
Thanks for your post. Which generator are you using for this particular column and are you rspecifying your own regular expression in this generator? How many rows have you told it to generate, and are you telling SQL Data Generator to truncate the table beforehand?
Thanks!
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
CHP
Joined: 27 Jul 2009
Posts: 5
|
| Posted: Wed Jul 29, 2009 3:22 pm Post subject: |
|
|
We are using the Foreign Key Generator for the ParentID field. All the fields (AccountID/ParentID and FinYearID) are GUID type.
Relationships exist in the database as follows: Account table has FinYearID which is a foreign key to FinancialYear table's ID field, AccountID which is unique within the set of accounts with the same FinYearID, and ParentID which contains the AccountID of it's parent account (has the same FinYearID).
We are asking the generator to delete data from table before generation. I am asking for 100 rows in the Account table and 3 rows in the FinancialYear table. I am asking it to repeat key values between 0 and 5 times. Even though the ParentID field is marked as Allow Nulls in the database, the "Allow null values" option in Data Generator is not available. I would actually like to ask for the generator to leave the ParentID field null for most records, but it won't let me. |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6347
Location: Red Gate Software
|
| Posted: Fri Jul 31, 2009 5:26 pm Post subject: |
|
|
Hello,
Do you get further if you generate more rows in the FinancialYear table? I think that there need to be more values to allow the createion of 100 unique rows in the other table that satisfy the foreign key constraint.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
CHP
Joined: 27 Jul 2009
Posts: 5
|
| Posted: Sat Aug 01, 2009 2:30 pm Post subject: |
|
|
| Unfortunately not, it happens even if I ask for 1000 rows in each table. |
|
| Back to top |
|
|
CHP
Joined: 27 Jul 2009
Posts: 5
|
| Posted: Thu Aug 06, 2009 6:47 pm Post subject: |
|
|
| Any thoughts? |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6347
Location: Red Gate Software
|
| Posted: Wed Sep 16, 2009 2:16 pm Post subject: |
|
|
Hi Debbie,
Here is the schema that I created to try to reproduce the date generation issue. Unfortunately I could get 3 rows into FinancialYear and 100 rows into Account.
| Code: |
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT N'Creating [dbo].[FinancialYear]'
GO
CREATE TABLE [dbo].[FinancialYear]
(
[AccountID] [uniqueidentifier] NOT NULL,
[FinancialYear] [int] NOT NULL
)
GO
PRINT N'Creating primary key [PK_FinancialYear] on [dbo].[FinancialYear]'
GO
ALTER TABLE [dbo].[FinancialYear] ADD CONSTRAINT [PK_FinancialYear] PRIMARY KEY CLUSTERED ([AccountID])
GO
PRINT N'Creating [dbo].[Account]'
GO
CREATE TABLE [dbo].[Account]
(
[FinYearId] [uniqueidentifier] NOT NULL,
[AccountId] [uniqueidentifier] NOT NULL
)
GO
PRINT N'Creating primary key [PK_Account] on [dbo].[Account]'
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ([FinYearId], [AccountId])
GO
PRINT N'Adding foreign keys to [dbo].[Account]'
GO
ALTER TABLE [dbo].[Account] ADD
CONSTRAINT [FK_Account_FinancialYear] FOREIGN KEY ([AccountId]) REFERENCES [dbo].[FinancialYear] ([AccountID])
GO |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
CHP
Joined: 27 Jul 2009
Posts: 5
|
| Posted: Wed Sep 16, 2009 3:55 pm Post subject: self referencing key |
|
|
Hi Brian,
Thank you for getting back to this. I think what was missing was the self-referencing part. Here is some sql to generate a simple version of the tables involved. LedgerID here is the ParentID I was talking about above. This should generate the error I was seeing.
| Code: |
/****** Object: Table [dbo].[TestAccount] Script Date: 09/16/2009 10:08:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Account](
[AccountID] [uniqueidentifier] NOT NULL,
[AccountNumber] [nvarchar](12) NULL,
[AccountName] [nvarchar](50) NOT NULL,
[AccountType] [int] NOT NULL,
[LedgerID] [uniqueidentifier] NULL,
[UniqueID] [int] IDENTITY(1,1) NOT NULL,
[Version] [datetime] NOT NULL,
[FinYearID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_AccountID_FinYearID] UNIQUE NONCLUSTERED
(
[AccountID] ASC,
[FinYearID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_AccountName_FinYearID] UNIQUE NONCLUSTERED
(
[AccountName] ASC,
[FinYearID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Account] WITH CHECK ADD CONSTRAINT [FK_LedgerID_AccountID] FOREIGN KEY([LedgerID], [FinYearID])
REFERENCES [dbo].[Account] ([AccountID], [FinYearID])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_LedgerID_AccountID]
GO
ALTER TABLE [dbo].[Account] WITH CHECK ADD CONSTRAINT [AccountTypeRule] CHECK (([AccountType]=(1) OR [AccountType]=(2) OR [AccountType]=(3)
OR [AccountType]=(4) OR [AccountType]=(5)))
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [AccountTypeRule]
GO
CREATE TABLE [dbo].[FinancialYear](
[FinYearID] [uniqueidentifier] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[Version] [datetime] NOT NULL,
[UniqueID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_FinancialYear] PRIMARY KEY CLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_FinYearGuid] UNIQUE NONCLUSTERED
(
[FinYearID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account] WITH CHECK ADD CONSTRAINT [FK_Account_FinancialYear] FOREIGN KEY([FinYearID])
REFERENCES [dbo].[FinancialYear] ([FinYearID])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_FinancialYear] |
The error is in the Account table: "Generation stopped. The generator for column LedgerID could not generate any more values. |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6347
Location: Red Gate Software
|
| Posted: Thu Sep 17, 2009 9:56 am Post subject: |
|
|
Hello again,
Thanks for the schema snippet, it does accurately reproduce the problem. My thought is that this is bug #SDG-700, regarding self-referencing foreign keys in the same table, same problem that another user had posted here: http://www.red-gate.com/messageboard/viewtopic.php?t=6882
I can let you know when we take action on this issue; for now, it's still under review.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
|
