I have two tables; A and B,

A.column1 has a unique constraint

B.OwnerID references to A.column1

LLBL doesn't recognize the relation. How can I introduce my relation to LLBL?

I've made a little search in the forum but

The PK side can't be a unique constraint (i.e surrogate key), it has to be a real PK.

Otis wrote:

The PK side can't be a unique constraint (i.e surrogate key), it has to be a real PK.

actually, I have a real PK but I can't use it for relationship purposes. it's illegal for my business logic.

it's an owner-object relationship, where an owner may have one or more objects. owner's PK is autoincremental. the owner has another field "uniqueID" which has a UC. object has a PK and also has a field "ownerID" which refers to uniqueID of its owner. I can't use the owner's PK in the relationship. that's why I use uniqueID. thought llbl would recognize the relationship.

do you plan to add this feature to llblgen?

A relation's PK side uses the PK as the PK is theoretically the identifying element for an entity. I wonder why you use an autoincrement PK (which is already unique) if you already have a uniquely identifying field, your UC field?

The reason this isn't added is that you then can have multiple relations defined between two entities where the PK side, which should be unique, isn't the same, i.e. isn't unique (as there are 2 different PK sides)

I have encountered this same problem in the database (ms access) I am using to play around with LLBLGen for the first time. I will try to attach the diagram to more easily understand the issue, but it is essentially the same:

I have a table with a natural primary key, but also with a surrogate key (autonumber, Indexed No Duplicates) that, for whatever reason ( for simplicity in this case), that is used for all relationships defined on related tables. This is certainly a valid design, and not all that unusual.

LLBLGen didn't pick up this relationship as I had expected, wheareas if I make this the primary key, it then picks it up.

If the column is defined as Indexed No Duplicates (actually, it must be defined as such to be involded in relationships), then I think LLBLGen should pick up this relation should it not??

Thanks

PS: I attached a jpg of the diagram....in the diagram, I am talking about table report_instance, whose primary key is report_code&report_period, but the surrogate key report_id is used to define relationships to all other subordinate tables.

Have been doing some reading of other posts and found a few more related ones:

Here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9564&HighLight=1 And here: "(relations on Unique contraints are not yet supported) -- sorry": http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=4855&HighLight=1

That last one sounds like this might be supported in the future?

I know it is an unusual design, but it is certainly valid (maybe not recommended except in special circumstances, but still valid). It is similar to the natural vs surrogate key debate, somewhat. Sometimes as a developer you don't have control over the design of the database, so your hands are totally tied in a situation like this.

I would really like to see it supported, at least as a configureable option?

Thanks!!!

My 2 cents:

The debate between Natural vs Surrogate keys, is about to use a Natural key as the PK or add a surrogate key (eg. Identity column) and use it as the PK. Sometimes Surrogate Keys are recommended if your Natural Key is a composite key.

As far as I know, which-ever PK you choose to be, you should use it in your relations with other tables. That's why Surrogate Keys were introduced, to ease relationships and enhance the JOINing performance.

Yes I was thinking about this last night, and I think you guys are right. I think my scenario is a legitimate one, but in my example, if I have chosen to change from the natural PK to a surrogate one, then from that point forward in the database, the new surrogate key will be representing a row in the table so it should most definitely be designated the PK, and , I can still define the former PK columns with a unique index to enforce the uniqueness. So I suspect I was not thinking too straight.

Thanks for the feedback!