I am using LLBLGen 3.5 with SQL Server Express 2008 and Management Studio 2008 Express Edition

My problem is that I am not able to create a 1:1 relationship (and unique constraint) between 2 entities in my model.

My two tables are:

1. CheckDefinition with PK = CheckId

2. CheckProcessingMap PK = ProcessingMapId FK = CheckId

On CheckProcessingMap I have a unique key on the CheckId column.

The model does not contain any unique constraint within CheckProcessingMap entity and the relationship CheckProcessingMap to CheckDefinition is m:1

If I add the unique constraint manually in the designer it is removed when I refresh the relational model with a message saying:

Post-migration actions performed on entity model and other project elements Migration of relationships and unique constraints • The unique constraint 'Ix_CheckProcessingMap' has been removed from entity 'CheckProcessingMap' because its underling unique constraint couldn't be found in the refreshed relational model data or it's now a unique constraint for a 1:1 relationship.

I have tried removing and re-adding the unique key on this table but the unique constraint is not reflected in the model no matter what I do.

I have other tables that use this exact same mechanism (as far as I can see) that correctly reflect a 1:1 relationship with a UC in the entity model. I have compared the table creation scripts generated by Management Studio and the only difference I can see between the table that works and the one that doesn't is that, on the table that is not read correctly, the unique key column is also a FK.

Can anyone suggest what might be wrong here?

• What build version nr are you using? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7721)

• Please post the DDL creation script for that table, includind the UC, of course.

Sorry for the delay and for not providing more detail at the beginning ..

I am using Version 3.5 Final Released May 31st 2012. Database is SQL Server 2008. Template is Self Servicing, .NET 4.0 C#

Here is the create script for the table ...

/****** Object:  Table [dbo].[CheckProcessingMap]   Script Date: 08/02/2012 14:55:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CheckProcessingMap](
    [ProcessingMapId] [bigint] IDENTITY(1,1) NOT NULL,
    [ProcessingCode] [varchar](50) NOT NULL,
    [CheckId] [bigint] NOT NULL,
 CONSTRAINT [PK_CheckProcessingMap] PRIMARY KEY CLUSTERED 
(
    [ProcessingMapId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY],
 CONSTRAINT [IX_CheckProcessingMap] UNIQUE NONCLUSTERED 
(
    [CheckId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CheckProcessingMap]  WITH NOCHECK ADD  CONSTRAINT [FK_CheckProcessingMap_CheckDefinition] FOREIGN KEY([CheckId])
REFERENCES [dbo].[CheckDefinition] ([CheckId])
GO

ALTER TABLE [dbo].[CheckProcessingMap] CHECK CONSTRAINT [FK_CheckProcessingMap_CheckDefinition]
GO

The DDL Update Script that LLBLGen designer keeps creating is :

-- ###############################################################################################################
-- DROP statements for elements no longer needed or replaced elements.
-- ###############################################################################################################

USE [dbname]
GO

ALTER TABLE [dbo].[CheckProcessingMap] DROP CONSTRAINT [IX_CheckProcessingMap]
GO

Try removing the etities from the model, refresh the catalog, and then remap them again. Also if this is a 1:1 relation, why you don't have the PK of the second table be the FK that reference the first table's PK, and so there should be no need for CheeckId column.

The UC is removed because ... there's no UC in the DB, while you do have defined one in the model. This means you have to specify one in the DB, or set Unique constraints follow dbunique constraints to false in the project settings under database first -> catalog refresher.

Defining a UC on the FK field makes the relationship a 1:1 relationship, as all FK field values are unique. It's made a m:1 relationship after the catalog refresh because there's no UC in the DB.

So the best you can do is this: right-click the relationship in project explorer and select edit and change the relationship to be a 1:1 relationship. Then check the 'model only' checkbox and click OK.

This means the relationship will be a 1:1 relationship but as it's 'model only' there's no UC required in the DB.

If you added a UC in the database, be sure it's a unique constraint, not a unique index.

Sorry for the delay .. been out of this area of the team for a while .. now back again.

Thanks Walaa - that is a good idea and makes sense. I presume the modelling engine will pick up this relationship correctly then?

Thanks for your feedback Otis. I was of the belief that I do have a unique constraint on this table due to the inclusion of the "CONSTRAINT [IX_CheckProcessingMap] UNIQUE NONCLUSTERED" part.

I have used exactly the same mechanism in other entities and got what I was expecting (that's not to say that it was the right way to do it but I just thought the results were consistent with my expectations).

I'll try the remove/add process on the entities in question although I'm pretty sure I have been through that cycle already.

I guess it's confusing and disturbing when a pattern works in one place in the project and not another. For my peace of mind, and to make sure I have a better understanding of what is happening, I'd like to work that inconsistency out.

Just check it out, and we'll be waiting your feedback, if removing and re-adding works or not.