Multi-part keys

slowder

Let's say you have a table with three columns that make up a unique constraint, Each of the columns is a foreign key to another table. You want to use data generator to insert data into those columns, but the combination of those columns must be unique. What set of switches do you use to ensure the data generated will not violate the unique constraint?

I'm sure I'm overlooking the obvious here.

Shannon

slowder

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

slowder

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.

Brian Donahue

If you are using composite keys, SQL Data Generator should deal with this automatically. For instance:

ALTER TABLE t1
    ADD CONSTRAINT pk_myConstraint PRIMARY KEY (businesskey1,businesskey2)

I think in order to answer this, the exact constraint definitions would be required. I can't say offhand why it is not working in your situation but whatever is happening can probably be worked around using the appropriate generator configuration for the columns.

slowder

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?

Brian Donahue

I was able to generate data against this schema without any errors. Filling in the blanks, here is the schema I created:

CREATE TABLE Family(
FamilyID INT IDENTITY(1,1) PRIMARY KEY,
FamilyName NVARCHAR(250)
)
CREATE TABLE Location(
FamilyID INT NOT NULL,
LocationID INT NOT NULL,
LocationName NVARCHAR(250)
)
ALTER TABLE Location ADD PRIMARY KEY(LocationID,FamilyID);

CREATE TABLE PersonCategory(
PersonCategoryID INT NOT NULL,
FamilyID INT NOT NULL
)
ALTER TABLE PersonCategory ADD PRIMARY KEY(PersonCategoryID, FamilyID);
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 PersonCategory(PersonCategoryID, FamilyID) 
     
 )