Change primary key field

Hi Jason

beside the principle that a PK has to be stable, it causes a conflict in a one-to-many situation.:
changing the PK of ONE means that the MAMY-records loose their link-point.

The proper way of doing it is:

- copy the ONE record to the new key

- redirect the MANY-records to the new ONE-record

- delete the old "ONE" record afterwards

And this is what you can do in Uniface.

Another reason: uniface identifies the record by the primary key (to retieve data etc.)

Success, Uli

and apparently that's too hard for uniface to do????

again, i don't see why it's so hard to implement o_O

you have outlined just one method of implementation, relationships are already defined within the app models. what more do you need?

complaining that a PK has to be stable and changing it causes a conflict in a one-to-many situation is, in my view, silly because in any application where there is dynamic data, or its inevitable for data change - this situation is likely to come up. it should ultimately be looked upon from the end users' perspective as that's who will be using (and paying!) for the application. therefore as an end user, not being able to change a field solely because it's a primary key is pretty weak.

Hi,

sorry, but since the days of Peter Chen, ven te Chow, and others Fathers of  the relational database.
PKs have an outstanding role in unambigouusly identifying database-rows.
if YOUR PKs are volatile, it is common best practise to use a "technical" PK instead.

Referential Constraints in the database rely on the stability of the PK etc.
So these are the rules of the game and the uniface driver as well.

As I demonstrated, you can use the
copytonew-and-relinktonew-and-deleteold
to achieve what you want to do

Success, Uli

P.S. Uniface NEVER demands that you have all entities of your database in your data-model 
nor does it assume all entities of a model are in the same database.

But perhaps you can build your own stored procedures to modify the database as you like

i knew this arguement would come up.

i'm not arguing against their work.

i'm wondering why uniface doesn't have anything to cater for changing a primary key value in such a way that it doesn't compromise data integrity.

Hi Jason,

if you have a development environment which is

- proprietory and
- bound to a single database only with
- access to all DDL definitions

you may have a functionality like the one you asked for.
Lets take ACCESS as an example (but I am not sure if it meets all criteria above).

Uniface does not demand any of the 3 points I mentioned above, so it is not possible to do so.
In the recent versions (AFAIK 8+), the database drivers block an update on the primary key fields.

But perhaps the guys from "the lab" have better news for you that I (as a CPWR outsider) can give.

Success, Uli

P.S. One option is that you define a couple of stored procedures and activate them via uniface like a
upd_PK_ent_ONE(oldpk,newpk)

Hi

changing pk is not that difficult, when the key is semantic, you can introduce a technical key for the associations. ($guid), a little modification in the LMK trigger is also needed.  changing the PK has under those conditions no impact.

Uniface can't refactor.
So, when you need some refactoring, export yoor model to XML format, search and replace the the things that you want to change. Modify the database by hand. (and import the modified XML file).

I don't have to say, that you should make a backup before you execute this type of actions.

Regards Dino Seelig appl|web architect ITIS
 

Just thought I'd throw the release/e command out there for your investigation

David

Hi David,

YES, release is the key to COPY a branch of the database.
Perhaps a release/mod would be even better because all
records already retrieved (!) will be added to the new ONE occurence, not only the one modified
Then you can delete the "old" branch with the previous PK.

So the sequence (which can be put into a service) is:

retrieve ONE and included MANY-frame with "old" PK
release/mod
change the PK.ONE
store
commit
retrieve ONE and included MANY-frame with "old" PK
remocc "ONE"
store
commit

Success, Uli

But uniface does not allow you to change the PK directly (which is the wish)

I 've found some ancient (17-Dec-98 !!!) text about this on Frontline:

How (not) to change the value of a primary key

Release : All

Last Updated :17-Dec-98

According to relational database theory, a primary key is a unique identifier for a record in a database. Once it has been created and stored it cannot be changed. To update a primary key, the record identified by the old primary key must be removed from the database, and a new record with the new primary key needs to be created.

Changing the primary key on a Uniface form component has never been allowed (message 0105, "Not allowed to change primary key field." appears). However, in previous Uniface releases (version 6) it was possible to change the value of a primary key using Proc code (for example,  PRIMARY_KEY=NewValue). This behavior is an undocumented feature and contradicts relational database theory, therefore it is not supported. The latest releases of Uniface Seven have improved validation rules and changing the value of a primary key using Proc code is no longer possible. The only correct way to change a primary key is as follows:

1. Copy all relevant data from the occurrence into a buffer (for example, by using putlistitems/occ  ListName, EntityName which puts all the data from an occurrence of the entity into a list).
2. Delete the record.
3. Create a new record.
4. Construct a new primary key for the new record.
5. Copy the relevant data from the buffer into the new record.

It can also be useful to use a release statement. The release statement releases a fetched occurrence from database control and therefore allows the primary key to be changed in the component. However, the record resulting from the primary key changes does not replace the old record in the database, but instead is inserted in the database as a new record. The old record still has to be removed from the database if it is not needed. The release statement is most useful when an identical, or a similar record (for example with only a different primary key), needs to be created.

Some RDBM systems allow updates of primary keys. However, to preserve database independence, Uniface does not allow this even when such databases are used.

Note: The need to change the value of a primary key is an indicator that the design of the database should be reevaluated. Since by definition a primary key is a stable, permanent, and immutable attribute, the need for it to be changed during the lifetime of an entity indicates a poor selection of an attribute as a primary key. Use of a technical key in such cases is highly recommended.