New Posts New Posts RSS Feed: Is changing Primary Keys supported?
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Is changing Primary Keys supported?
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
jradxl View Drop Down
Newbie
Newbie


Joined: 14-Jun-2011
Posts: 30 		Post Options Post Options   Quote jradxl Quote  Post ReplyReply Direct Link To This Post Topic: Is changing Primary Keys supported?
    Posted: 25-Jun-2011 at 3:30pm
Hi,
Does DF (or EF) support the changing of Primary [Composite] Key(s)?

Consider a Table with these three fields as a Composite Key.
1. EmployeeID
2. Date
3. SequenceNumber
as used in a HR system.
The User might (frequently in my experience) miss-key the date, and then has to correct the record just entered.

We can assume that the EmployeeID will not change (if that was wrong, the User is editing the wrong person, and will delete the row themselves), but the User would be expect to be able to change the Date and/or the SequenceNumber.

Do I need to code this in an EntityServerSaveInterceptor?

thanks
John
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 27-Jun-2011 at 12:06pm
Hi John;

Unfortunately, no, it's not supported, but there is a workaround. We had to respond to another customer having a similar question the other day. Here's a copy paste of that response written by Ward Bell. The workaround is provided at the end.

#1   An application cannot change the primary key (PK) of an existing (already saved) entity.

 

It makes no difference whether the PK is composite or single valued … you just can’t do it.

 

My recollection is that EF prohibits changes to the PK but I’m not certain. We do know that allowing a change to the PK invites numerous unfortunate consequences when the PK of the changed entity is the foreign key of other, child entities.  Consider the effect on Order-Line-items of changing the ID of its parent Order. The database could detect a FK violation and throw an exception; it might not in which case you’ve orphaned the child line items.

 

We agree that DF does not properly report the error when the application tries to change the PK

 

DF defect #1: We should throw a clear exception when you try to save an updated entity with a pending change to the PK. Instead, the save appears to succeed although it did not.

 

DF defect #2: We do throw an exception if you try to save an updated entity with a pending change to the PK and at least one other property. Unfortunately, (a) we rely on the server to produce the exception when we could detect it on the client and (b) the wording of the exception is not clear about the cause or the resolution.

 

We will correct these defects in our next release. But our no-PK-change rule is a deliberate choice in DF; we feel the defects are in the reporting, not in the prohibition itself.

 

What to do?

 

All architects agree that one should never change the primary key of an existing entity. That said, we know that it happens, especially with legacy databases in which the key contains a meaningful value … such as some portion of an entity’s “name”.

 

If the app must be able to change the entity PK, the only permitted way to do that is

                 ·        copy the entity,

                 ·        give it the revised PK,

                 ·        delete the original.

 

You can do this all in the same EntityManager and submit these changes in the same save (so they are committed in the same transaction).

 

This insert+delete approach is open to the same risks as I mentioned above: potential FK violations and orphaned children. But at least you had to work at “doing the wrong thing”.
Back to Top
jradxl View Drop Down
Newbie
Newbie


Joined: 14-Jun-2011
Posts: 30 		Post Options Post Options   Quote jradxl Quote  Post ReplyReply Direct Link To This Post Posted: 27-Jun-2011 at 3:52pm
Thanks Denis,
Yes, it was Defect #1 that I couldn't believe, and so true about Defect #2's message being confusing.
I only hope you don't add exceptions in non-accessable places so users of your libraries like me cannot intercept these changes.
For me I hope I can still use EntityServerSaveInterceptor.

I think a paragraph in your documentation would be advisable, as my searches didn't come up with anything that convinced me what the current situation is.

rgds
John
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 27-Jun-2011 at 4:39pm
Thank you for the feedback John. We already have some write up regarding this but instead of using the word "primary key", we had used the word "EntityKey" which is DevForce implementation of an entity's primary key.


I'll submit a request to improve the DRC search engine for this.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down