Multi-part keys

slowderslowder Posts: 7
edited July 30, 2014 10:07AM in SQL Data Generator
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

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 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
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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) 
         
     )
    
Sign In or Register to comment.