Hi,

I'm trying out the new inheritance feature which looks fantastic.

However I'm stuck.

I have the following Assignee Table (AssigneeID PK) Group Table (GroupID UniqueK, AssigneeID PK) User Table (UserID UniqueK, AssigneeID PK)

ie a user or a group can be assigned to a particular task.

Now when I construct the DB as follows I can see the inheritance when I do a "Construct Target-per-entity ..." however I then get the following error messages: CreateNewProject::FK constraint FK_Account_User has been removed because it is not a correct FK constraint. Reason: One or more fields on the PK side of the FK aren't marked as PK in the table. Likely caused by defining the FK on the PK table. CreateNewProject::FK constraint FK_CommunicationProfile_User has been removed because it is not a correct FK constraint. Reason: One or more fields on the PK side of the FK aren't marked as PK in the table. Likely caused by defining the FK on the PK table. CreateNewProject::FK constraint FK_GroupMember_Group has been removed because it is not a correct FK constraint. Reason: One or more fields on the PK side of the FK aren't marked as PK in the table. Likely caused by defining the FK on the PK table. CreateNewProject::FK constraint FK_Project_User has been removed because it is not a correct FK constraint. Reason: One or more fields on the PK side of the FK aren't marked as PK in the table. Likely caused by defining the FK on the PK table. CreateNewProject::FK constraint FK_Task_User has been removed because it is not a correct FK constraint. Reason: One or more fields on the PK side of the FK aren't marked as PK in the table. Likely caused by defining the FK on the PK table. CreateNewProject::FK constraint FK_Template_User has been removed because it is not a correct FK constraint. Reason: One or more fields on the PK side of the FK aren't marked as PK in the table. Likely caused by defining the FK on the PK table. CreateNewProject::FK constraint FK_UserRole_User has been removed because it is not a correct FK constraint. Reason: One or more fields on the PK side of the FK aren't marked as PK in the table. Likely caused by defining the FK on the PK table.

So essentially I need to make the AssigneeID as the PK but then it invalidates my FK's so I can do things like have a UserRole join table and then be able to do UserEntity.UserRoles (to get the userroles collection).

How do I work around this?

Changing to DB to: I have the following Assignee Table (AssigneeID PK) Group Table (GroupID PK, AssigneeID UniqueK) User Table (UserID PK, AssigneeID UniqueK) Yields the correct relations but then I don't get my inheritance.

Thanks Paul

The following solves the inheritance:

Assignee Table (AssigneeID PK) Group Table (GroupID UniqueK, AssigneeID PK) User Table (UserID UniqueK, AssigneeID PK)

And to solve your relations problem, build those relations on the UserID (Unique Constraint) & GroupID (Unique Constraint)

You can have Foreign Keys refering to Unique Constraint Fields.

Hi Walaa,

Thanks for the response.

When you say build the relations - do you mean I need to build them by hand in the Gen Pro interface since the FK's I've created won't be recognised when I sync the model to the DB?

Paul

Actually I don't see how I can add it - the only way this is possible is if 1 of the fields you are relating in the entity is a PK however in this scenario I don't have a PK.

eg I want to have a relation between user and account.

accountid is the PK on account assigneeid is the PK on user

so it won't let me create the relation in the designed to match account.userid_manager to user.userid

Can you explain further what you mean?

Sorry for not being so descreptive.

I meant building them in your database, so you will have other tables referencing User & Group tables on their Unique Constraint Fields.

But forget this answer as this relation won't be available in LLBLGen Pro (relations on Unique contraints are not yet supported) -- sorry

Consider the following:

You need User & Group to inherit from Assignee, if they are really of type Assignee.

i.e. a User is an Assignee, and a group is an Assignee, in that case AssigneeID in the User & Group Tables is indeed a valid logical PK

and you should use it to reference those tables from another tables like Account.

If not then this is not an inheritance relation between User & Assignee (logically speaking), and then you should not try to implement inheritance between them.

Please read the following section "Pitfalls with inheritance" under "Concepts - Entity inheritance and relational models" in the LLBLGen Pro documentation

I have "solved" it.

LLBL Gen Pro doesn't appear to support FK's to UC's. Howeverw i have altered the model such that I use the PK field. It isn't a huge problem since the PK and the UC have a 1-1 relationship however I don't like it. It's not what I would want to do in my DB to make it readable.

However it probably does work for performance reasons.

I now have: Assignee (AssigneeID PK) Group (AssigneeID PK, GroupID UC) User(AssigneeID PK, UserID UC)

Now my FK_User_UserRole now joins User.AssigneeID to UserRole.UserID

It's ok but I would like to not have assignee everywhere but use userid when I want to specify a user and assignee id when I want to specify either user or group or ???

Thanks Paul

Avoid surrogate keys in general, you don't need 2 identifying fields on a single entity, 1 is enough, which then will become your PK. That's also the reason why FK's to UC's aren't supported.

Otis wrote:

Avoid surrogate keys in general, you don't need 2 identifying fields on a single entity, 1 is enough, which then will become your PK. That's also the reason why FK's to UC's aren't supported.

Database guru's and DBA's around the world are groaning!

BOb

pilotboba wrote:

Otis wrote:

Avoid surrogate keys in general, you don't need 2 identifying fields on a single entity, 1 is enough, which then will become your PK. That's also the reason why FK's to UC's aren't supported.

Database guru's and DBA's around the world are groaning!

Well, give me one reason why FK's to surrogate keys are required.

Otis wrote:

pilotboba wrote:

Otis wrote:

Avoid surrogate keys in general, you don't need 2 identifying fields on a single entity, 1 is enough, which then will become your PK. That's also the reason why FK's to UC's aren't supported.

Database guru's and DBA's around the world are groaning!

Well, give me one reason why FK's to surrogate keys are required.

All my FK's point to surrogate keys. The other option is a business key, right?

So, if I have

OrderHeader table Order_ID int (surrogate key) OrderNumber varchar(20) (business key)

OrderDetail table OrderDetail_ID int (surrogate key) Order_ID int (FK to OrderHeader)

perhaps you mean FK's to Candidate keys that are not the PK?

BOb

perhaps you mean FK's to Candidate keys that are not the PK?

Yes, that's what I meant. You have a PK and also a second field with a UC which is the surrogate key. Often this happens in tables which have an artificial PK and a readable non PK. It's then better to use teh artificial, real PK in FK's and not the readable non-PK (although it has a UC)

Otis wrote:

perhaps you mean FK's to Candidate keys that are not the PK?

Yes, that's what I meant. You have a PK and also a second field with a UC which is the surrogate key. Often this happens in tables which have an artificial PK and a readable non PK. It's then better to use teh artificial, real PK in FK's and not the readable non-PK (although it has a UC)

Ah, ok. whew I didn't think you were advocating away from surrogate keys. I've always understood the terminology as:

Surrogate key: unique identifier that is not an attribute of the entity in question Natural key: unique identifier that is an attribute of the entity in question Candidate key: any identifier that is unique and can thus be considered a PK

I definitely lean toward the surrogate key side of the debate as I think it allows code to be cleaner, opens up more opportunities for reusable code, and eliminates the problems of key changes. But that's just me.

Jeff...

jeffreygg wrote:

Otis wrote:

perhaps you mean FK's to Candidate keys that are not the PK?

Yes, that's what I meant. You have a PK and also a second field with a UC which is the surrogate key. Often this happens in tables which have an artificial PK and a readable non PK. It's then better to use teh artificial, real PK in FK's and not the readable non-PK (although it has a UC)

Ah, ok. whew I didn't think you were advocating away from surrogate keys. I've always understood the terminology as:

Surrogate key: unique identifier that is not an attribute of the entity in question Natural key: unique identifier that is an attribute of the entity in question Candidate key: any identifier that is unique and can thus be considered a PK

ah, sorry I caused any confusion here I always thought 'surrogate' key was the term for a field which is unique (and thus can be used for identifying the row) but isn't the PK. Natural and artificial keys are not related to that.

I definitely lean toward the surrogate key side of the debate as I think it allows code to be cleaner, opens up more opportunities for reusable code, and eliminates the problems of key changes. But that's just me. Jeff...

The debate of having a natural key or a artificial key is really old and as always with these things: there's no consensus. 'it depends' is (as with a lot of things related to databases) also applicable here: sometimes an attribute is a true unique value that won't change, so an ideal candidate for using as a PK. However often that's not the case and an artificial key has to be used. Good examples of horror-scenario's are 'full name' as PK for a person, or even the SSN.

Otis wrote:

ah, sorry I caused any confusion here I always thought 'surrogate' key was the term for a field which is unique (and thus can be used for identifying the row) but isn't the PK.

I have always called that and heard it called the "candidate" key.

Otis wrote:

Natural and artificial keys are not related to that.

The basic definition of the word "Surrogate" is "A substitute".

I think what you are calling "artificial" keys is what is known as a "surrogate" key.

Dr. E. F. Codd, the creator of the Relational Model defined a surrogate key as "..Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them ..."(Dr. Codd in ACM TODS, pp 409-410)

Thanks, I now know the proper terms