Comments
Sort by recent activity
Here's an example where the data generator struggles. CREATE TABLE [dbo].[Story]
(
StoryID INT IDENTITY(1,1) NOT NULL
, FamilyID INT NOT NULL
, Name NVARCHAR(255) NOT NULL
, [Description] NVARCHAR(MAX) NULL
--redacted columns
, [LocationID] INT NULL
, [PersonCategoryID] INT NOT NULL
, [Published] BIT NOT NULL DEFAULT(1)
, CONSTRAINT PK_Story__StoryID_FamilyID
PRIMARY KEY (StoryID, FamilyID)
, CONSTRAINT FK_Story_Family__FamilyID
FOREIGN KEY (FamilyID)
REFERENCES Family(FamilyID)
, CONSTRAINT FK_Story_Location__LocationID_FamilyID
FOREIGN KEY(LocationID, FamilyID)
REFERENCES Location(LocationID, FamilyID)
, CONSTRAINT FK_Story_PersonCategory__PersonCategoryID_FamilyID
FOREIGN KEY (PersonCategoryID, FamilyID)
REFERENCES Person.PersonCategory(PersonCategoryID, FamilyID)
)
The problem is around the foreign keys on the FamilyID, LocationID and PersonCategoryID. Values in the LocationID field have to be for the same family (tenant). Values in the PersonCategoryID also have to be in the same tenant. So the data generator would have to understand, if I put 1 in the FamiylID, then I have to choose LocationIDs and PersonCategoryIDs that also have familyID of 1. I haven't found a way to do that with data generator.
Also, I've found if you have a self-referencing key (say "parentStoryID" and that points back to the PK in this table, StoryID + FamilyID, that fails too.
Any ideas on how to configure for these situations? / comments
Here's an example where the data generator struggles.CREATE TABLE [dbo].[Story]
(
StoryID INT IDENTITY(1,1) NOT NULL
, FamilyID INT NOT NULL
, Name NVARCHAR(25...
After further testing, I think the issue has less to do with a unique around multiple columns, but multi-part keys.
In my database designs I have to account for multiple tenants in a single database. So every tenant-specific table has two parts to the key. The business key (usually an identity column) plus the tenantkey. Both columns are required in where clauses and join clauses.
I think data generator might not understand you can't just look up the one column from the parent table, you have to lookup both columns and make sure the business key has the same tenant key as you're trying to fill into the child table. (this may not be clear, and if not let me know and I'll try to explain better)
Is there a way to do muti-part keys with data generator?
Shannon / comments
After further testing, I think the issue has less to do with a unique around multiple columns, but multi-part keys.
In my database designs I have to account for multiple tenants in a single databas...
Yet more to consider: I can get one composite key to work, but not two int he same table. Consider this table:
CREATE TABLE t1 (
tenantKey INT
, businessKey1 INT
, businessKey2 INT
)
--fk1 : tenantkey + businesskey1
--fk2: tenantkey + businesskey2
I can fill tenantkey and businesskey1, if I set the generator to only put nulls in businesskey2. but if I try to enable both, I get an error on insert. / comments
Yet more to consider: I can get one composite key to work, but not two int he same table. Consider this table:
CREATE TABLE t1 (
tenantKey INT
, businessKey1 INT
, businessKey2 INT
)
--fk1 : tena...