Composite Foreign key with a calculated column

AbeAxiomatic · June 26, 2019 7:38PM

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?

Alex B · June 28, 2019 3:27PM

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

AbeAxiomatic · June 28, 2019 3:39PM

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 B · July 17, 2019 3:17PM

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
<pre class="CodeBlock"><code>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

AbeAxiomatic · July 17, 2019 5:30PM

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

Alex B · October 30, 2019 4:35PM

Hi @AbeAxiomatic,

The development team have reviewed issue SDG-921 as part of a periodical review and prioritization of open issues and, unfortunately, they are unable to fix it at this time.

When reviewing bugs the development team take into account the impact it has and the number of users affected and unfortunately we are unable to fix all bugs and have to prioritize fixing the ones that cause the greatest impact.

Thank you for your understanding.

Kind regards,

Alex

Composite Foreign key with a calculated column

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk