Multi-part keys

• 
  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 July 28, 2014 20:04

  - 0 +
• 
  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.

  slowder July 28, 2014 20:51

  - 0 +
• 
  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.

  Brian Donahue July 30, 2014 06:20

  - 0 +
• 
  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?

  slowder July 30, 2014 09:17

  - 0 +
• 
  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) 
       
   )
  

  Brian Donahue July 30, 2014 10:07

  - 0 +

Add comment

Please sign in to leave a comment.