Hi,

First of all, I would like to tell you that I've read as much as I could on this forum about my issue and I found a similar situation though the responses that I've seen overthere didn't helped me too much.

In few simple words - I have an entity called PageTranslationFlow which has a field called LivePageTranslationId which is an FK to another table and also is an UK. Based on this information, LLBL generates me an 1:1 relation - perfect so far - everything is great. What I don't understand is why (?????) this UK is not also listed as unique key in LLBL designer and also (this is what kills me) why I cannot access it in my template (lpt) through this.currentEntity.UniqueConstraints property?

What I've found so far is the following situation on this forum

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21066&HighLight=1

which describes also perfect my situation:

• the UK is in the database
• I'm adding it manually in the designer
• on refresh it dissapear with the following message - The unique constraint 'UkPageTranslationFlowLive' has been removed from entity 'Entities.PageTranslationFlow' 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 - in my case the situation is the second (1:1 relation).

To be honest I don't like the situation where I have to take a look in the relations (1:1) and extract the data from there...

Do I have any other way of keeping that UK in the designer or any other way to "discover it" in the LPT templating system?

Thank you, Evdin

PS: I'm using LLBL 4.0 Final.

Is it a Unique Constraint or a Unique Key?

Walaa wrote:

Is it a Unique Constraint or a Unique Key?

It's an Unique Key. Why? So far on other tables if I have an unique key but without FK on this column, I see it as unique key (both designer and template).

Thanks.

Please use terminology which is not ambiguous: Unique Key, is that a unique constraint next to a primary key? or is that a primary key? Or is it a unique index?

If it's a unique constraint next to a primary key (so other fields form the primary key), the FK points to a UC, not to a PK. FKs pointing to a UC aren't supported. Hence our question.

Otis wrote:

Please use terminology which is not ambiguous: Unique Key, is that a unique constraint next to a primary key? or is that a primary key? Or is it a unique index?

If it's a unique constraint next to a primary key (so other fields form the primary key), the FK points to a UC, not to a PK. FKs pointing to a UC aren't supported. Hence our question.

Sorry if I was not very clear in my description.

First of all, in my table I have a column called live_page_translation_id which is not a primary key - is just an int column. This column is also a UNIQUE KEY (not UNIQUE INDEX). This column is a foreign key to another table - linked to a primary key not to a UC.

So as a short structure I have like this:

page_translation (table)

id - int - PK page_name - nvarchar(200)

page_tanslation_flow (table)

id - int - PK live_page_translation_id - int - FK to page_translation (id) - UK (UK_page_translation_flow_live) ... other columns....

So, this makes LLBL creates the relation between page_translation_flow and page_translation as being a 1:1 relation (which is perfectly normal and great) but the UK_page_translation_flow_live is not listed as unique key in the designer and also is not visible by the template system (lpt). If I'm trying to add it manually in the designer, then the templating system acts normally but on the next refresh I'm loosing the UK with the following message:

The unique constraint 'UkPageTranslationFlowLive' has been removed from entity 'Entities.PageTranslationFlow' 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.

Again, please, accept my apologize if I wasn't very clear.

Evdin

Ok. So I presume this is mysql? (As Unique Key is a term used with mysql for unique constraint).

Could you post the DDL SQL (to create the tables in a db, so the CREATE TABLE statements) to create the tables so we can try to reproduce what you see? Please generate these DDL SQL statements from your DB system, not from the llblgen pro project. Also specify which DB type and version you're using.

Otis wrote:

Ok. So I presume this is mysql? (As Unique Key is a term used with mysql for unique constraint).

Could you post the DDL SQL (to create the tables in a db, so the CREATE TABLE statements) to create the tables so we can try to reproduce what you see? Please generate these DDL SQL statements from your DB system, not from the llblgen pro project. Also specify which DB type and version you're using.

SQL Server 2008 R2 - the DDL scripts will follow in few minutes.

Otis wrote:

Ok. So I presume this is mysql? (As Unique Key is a term used with mysql for unique constraint).

Could you post the DDL SQL (to create the tables in a db, so the CREATE TABLE statements) to create the tables so we can try to reproduce what you see? Please generate these DDL SQL statements from your DB system, not from the llblgen pro project. Also specify which DB type and version you're using.

The script is attached - there are 5 tables so you can see exactly what is happening.

Thank you.

PS: SQL Server 10.50.2550

The script indeed defines it, and I see 1 UC for page_translation_flow in the catalog explorer in the llblgen pro project, namely UK_page_translation_flow_live, the one on live_page_translation_id. When I reverse engineer the tables, I get a proper 1:1 relationship between PageTranslationFlow and PageTranslation over LivePageTranslationId.

When I refresh the relational model data after that, nothing is removed.

I'll attach the project I've created. Perhaps I misunderstood what you meant, but as far as I can see, everything works.

Otis wrote:

The script indeed defines it, and I see 1 UC for page_translation_flow in the catalog explorer in the llblgen pro project, namely UK_page_translation_flow_live, the one on live_page_translation_id. When I reverse engineer the tables, I get a proper 1:1 relationship between PageTranslationFlow and PageTranslation over LivePageTranslationId.

Everything what you've done is perfect accordingly to what I've also done. On this part I have one issue:

Why on the "Unique constraints" tab of the Entity Edit page (for PageTranslationFlow entity) the UK_page_translation_flow_live is not listed?

Otis wrote:

When I refresh the relational model data after that, nothing is removed.

I'll attach the project I've created. Perhaps I misunderstood what you meant, but as far as I can see, everything works.

Because of the fact that the UC is not listed, I had to manually create it in the designer. After that, if I do a refresh (after any other change to the db), is telling me that the UC has been deleted because the UC designates the 1:1 relation or the UC is missing (not the case).

The message from the log is:

The unique constraint 'UkPageTranslationFlowLive' has been removed from entity 'Entities.PageTranslationFlow' 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.

evdinursan wrote:

Otis wrote:

The script indeed defines it, and I see 1 UC for page_translation_flow in the catalog explorer in the llblgen pro project, namely UK_page_translation_flow_live, the one on live_page_translation_id. When I reverse engineer the tables, I get a proper 1:1 relationship between PageTranslationFlow and PageTranslation over LivePageTranslationId.

Everything what you've done is perfect accordingly to what I've also done. On this part I have one issue:

Why on the "Unique constraints" tab of the Entity Edit page (for PageTranslationFlow entity) the UK_page_translation_flow_live is not listed?

Ah!

That's by design because it's implicit: it's tied to the relationship being 1:1. If you could change/remove the UC, the relationship type would change automatically. See: http://www.llblgen.com/documentation/4.0/Designer/hh_goto.htm#Functionality%20Reference/EntityEditor_FieldsTab.htm

Otis wrote:

When I refresh the relational model data after that, nothing is removed.

I'll attach the project I've created. Perhaps I misunderstood what you meant, but as far as I can see, everything works.

Because of the fact that the UC is not listed, I had to manually create it in the designer.

No you don't have to The relationship is 1:1, and the UC is used for that. Creating one manually creates a duplicate, so you don't have to do that.

Please see the "Unique Constraints sub-tab" section in the doc page I linked to above.

Hope this answers the questions you had.

First of all - thank you for taking the time for the clarifications.

Second of all - please help me with a hint or a clue - the reason for which I had to create the UC manually again in the designer was to be able to "see it" in the template system. Do I have any other way to discover this kind of UC in the templating system (lpt)?

Thank YOU!!!!!!

Obtain the NormalRelationshipEdge object, which represents the entity relationship, and then call on it the method GetUniqueConstraintFormedByFkSide(), which returns the UC formed by the fk side if it's a 1:1 relationship. This is a model UC, so it contains the model fields (entity fields).

Please check the designer reference manual for details on methods on the objects you can obtain in templates.

Hope this helps

Otis wrote:

Obtain the NormalRelationshipEdge object, which represents the entity relationship, and then call on it the method GetUniqueConstraintFormedByFkSide(), which returns the UC formed by the fk side if it's a 1:1 relationship. This is a model UC, so it contains the model fields (entity fields).

Please check the designer reference manual for details on methods on the objects you can obtain in templates.

Hope this helps

Thank you very much for your help. Really, you really gave me a very important hint on how to solve this issue.

And for the generations that will come and use LLBL templating system this is how you will get all UC (both from 1:1 relations and also from the entity definitions):

this.currentEntity = (EntityDefinition)_activeObject;
    this.entityMapping = _executingGenerator.ProjectDefinition.GetGroupableModelElementMapping(currentEntity, _executingGenerator.DriverID);
    Project currentProject = _executingGenerator.ProjectDefinition;
    var allRelationshipInfosToTraverse = GeneratorUtils.GetAllRelationshipInfosForEntity(_executingGenerator, currentEntity);   

UniqueConstraintList uniqueConstraints = new UniqueConstraintList();
        uniqueConstraints.AddRange(this.currentEntity.UniqueConstraints);
        
        foreach(var relationshipInfo in allRelationshipInfosToTraverse)
        {
            if(relationshipInfo.NavigatorIsHidden || relationshipInfo.IsOnPkSide)
            {
                continue;
            }   
            
            if (relationshipInfo.RelationshipType == EntityRelationshipType.OneToOne)
            {
                if (relationshipInfo.NormalRelationship == null)
                    continue;
                
                SD.LLBLGen.Pro.ApplicationCore.EntityModel.UniqueConstraint localConstraint = relationshipInfo.NormalRelationship.GetUniqueConstraintFormedByFkSide();
                if (localConstraint == null)
                    continue;
                
                uniqueConstraints.Add(localConstraint);
            }
        }

Please, if you think that something is not right, let me know and also, again THANK YOU!