Composite Foreign key with a calculated column

I have 2 tables - 1 is a superset of the other. The child has a calculated column on the second half of the composite shared key.
  • dbo.Entity is the parent table with a Primary Key on (EntityId, EntityTypeId). EntityTypeId is an FK to ref.EntityType
  • entity.Person represents the subset, has a Primary Key on EntityId, a  persisted calculated column EntityTypeId = 10 (Corresponds to ref.EntityType record for 'Person'), and a Foreign Key Constraint (EntityId, EntityTypeId) to dbo.Entity.
I'm getting a failure on INSERT on entity.Person from the Data Generator due to Key violation. It recognizes the calculated column successfully, but I suspect that it's still trying to generate entity.Person records with an EntityId that doesn't fit into the composite constraint of entity.Person (EntityId, 10) -> dbo.Entity (EntityId, EntityTypeId). Is there a way to get the SQL Data Generator to work with this design?

Answers

  • Alex BAlex B Posts: 757 Diamond 1
    Hi @AbeAxiomatic,

    Unfortunately, SQL Data Generator doesn't handle composite primary keys as it will not generate data for a unique index where it sees the possibility that a duplicate can be generated.  It will only allow generation if one of the two columns is set to unique. 

    This is listed internally as SDG-921 and I have put a reference against your post here in that issue.

    I will update here when I have further information!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • AbeAxiomaticAbeAxiomatic Posts: 3 New member
    I tried adding explicit unique constraints on both tables, and arrived at the same issue. Here are my abbreviated table definitions:

    CREATE TABLE [dbo].[Entity] (
        [EntityId] INT NOT NULL IDENTITY(1,1),
        [EntityTypeId] TINYINT NOT NULL CONSTRAINT FK_Entity_EntityType REFERENCES ref.EntityType (EntityTypeId),
    CONSTRAINT PK_Entity Primary Key (EntityId, EntityTypeId),
    CONSTRAINT UC_Entity UNIQUE (EntityId)
    );

    CREATE TABLE [entity].[Person] (
        [EntityId] INT NOT NULL,
    [EntityTypeId] AS CAST(10 AS TINYINT) PERSISTED NOT NULL,
        [FirstName] NVARCHAR (50) NOT NULL,
        [LastName] NVARCHAR (50) NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (EntityId, EntityTypeId),
    CONSTRAINT FK_Person_Entity FOREIGN KEY (EntityId, EntityTypeId) REFERENCES dbo.Entity (EntityId, EntityTypeId),
    CONSTRAINT UQ_PERSON UNIQUE (EntityId)
    );

  • Alex BAlex B Posts: 757 Diamond 1
    Hi @AbeAxiomatic,

    We're still not quite sure what's going wrong here, it does seem like we're doing something wrong with the composite foreign key.

    We've managed to work around it by using a SQL Statement generator on entity.Person.EntityId that connects to the same database with the query 
    SELECT EntityId FROM dbo.Entity WHERE EntityTypeId = 10This ensures that the foreign key is respected.

    I'll update here further when I have any more information on the original generator having an issue.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • AbeAxiomaticAbeAxiomatic Posts: 3 New member
    Thanks for the follow-up! I'll give this a try as soon as I get a chance. It should help me create a more useful data set for my developers
Sign In or Register to comment.