Updating Primary Key in a Table

[rkumar]

I need help on updating a primary key in a table.

My table has four fields which make up a Primary Key ( Driver- AN8, Route Number, Route Date and Market). Sometimes user needs to change the driver-an8 and keep rest of the primary key same. How can I update the AN8.

Thanks,

Raj

B732 on NT,SP 19, SQL Sever 2000

 

[clee]

Insert new set of records with the new value and then delete the old
set. Or drop the index, update the column, and re-index.



Chi Lee
IT Project Leader, Va International Terminals, Inc.
B7331 SP11.3, NT4 ES/DS, Citrix, Win2K clients, SQL Server7

 

[hseider]

I think that an update will not work. You will have to do an add of the record with the new key and a delete of the record with the old key to get the desired results.

Hal

 

[neil_shepherd]

Hi Raj,

Does this table have another field or set of fields you can use as another unique key? If not (which is probably the case) could you add another field, maybe based on data item UKID and populate this with an incremental number. You could then use this as a unique identifier for the record and update AN8. JDE use this technique on tables such as F4111 and F0030.

Regards

Neil.

 

[Pon]

I would think you need to change the keys on your table. Sounds like the AN8 is your primary key and, the others are secondary keys.

 

[rkumar]

I want AN8 to be part of the primary key. Spec calls for more than one Driver(AN8) on a same route, same route date, and on a same market. Primay key will be unique only when AN8 is different.

 

[cavelasquez]

I think.......Create an other index (not unique), retrieve the record you
want, change AN8 part the primary key then update the table. (always using
the not unique index and AN8 is belongs to it)

 

[Pon]

What if you made all the keys (except AN8) primary and then made AN8 a secondary key. That way, you could keep all the other information the same while being able to change AN8. Will that work?

 

[BBritain]

Ok, I'm confused.....I just created a test table with 4 keys, test view, and test application with a Find/Browse and Headerless/Detail. I then added multiple records, verified them in the Find/Browse and then changed key fields with no problem. Also, the Add new record with new key and Delete the old record is not a bad idea. Is there still a problem here?

Ben again

 

[DBohner-(db)]

Ben,

Just to confirm... when you created the table, you did create the index?

What platform are you on? It doesn't say on the foot of your mails (might want to update that).

Depending on the platform.... If you don't create the index, there is no KEY - thus, you could change things at will.

Daniel

 

[BBritain]

Well, I just tested it out, 12/21/02, on Xe on AS400, but first came across this long, long ago (B7.3.2 or B7.3.3) on a unix server (HP3000?) and (I BELIVE but not sure) that I have also done this on a SequelServer using Sequel7. I'm not sure if those are the right terms.

Have you (I wanted to say "you all"), or any of you try updating keys with a Headerless/Detail (Hdls/Dtl) and had it not work?

( I only noticed the following on the AS400 and never had a chance to go back and look at the other systems)
The Hdls/Dtl form has an interesting feature to it, that regardless of the fields in the grid, the form will update all the fields in the business view even when they are not in the grid. And, the update will assume that those missing grid columns are blank. Imagine my suprise when fields were getting updated and not even in the grid! I also found that form level filter fields tended to update to the database, so if you're going to test my theory then you'll want to leave the filter fields off of the form and stick to the QBE.

** Note - I typically verify my statements before posting them on this site. In this case, I verified the ability to change keys only and have NOT verified this last paragraph to see if it only occurs in certain circumstances.

Here are the steps I went through to test this out.
1) Created test TBLE with 4 key fields and two non-key fields. Generated TBLE.
2) Created test BSVW with all columns.
3) Created test APPL with Find/Browse with all columns. Select Form/Exits (I know this is not good practice having a Row type exit interconnecting to a form without filtering) to a Hdls/Dtl form with only a grid for all columns, QBE added and a Find button added. And then I tested it. Creating this test took less time than I took to write this reply. So how about it? Anyone want to try this on their system and see if it works?

Ben again
I know I should post my system info here
but it keeps changing and I can never remember
what it is, so I only look it up when I ask a question.

 

[BBritain]

Daniel,

I guess I better respond to the question of keys/index. When I create the file and generate it - my understanding is that generating the table also generates the indices. Now I can't get out of TDA without specifying a primary key, and on previous projects I have verified that the indices were generated on past systems (although not this one, and not on an AS400) when generating the table. So if there is a way (in OW) to create a index-less table I'm not sure how to do it.

 

[got_to_love_jde]

Hi Ben,
I must say I thought you were mistaken but it was me. When you generate a table, the primary index is, at the very least, required and the the primary index is generated along with the table. Been generating the table and then the indexes seperately for so long I thought for sure they were seperate, required options.
Dave

 

[DBohner-(db)]

Ben,

What he said...

So... why do we generate indexes?

Daniel

 

[BBritain]

Well,

You gotta remember that we add indices to tables all the time, and when we want those indices to take effect AND not wipe out the table, we need a function to just wipe out ALL the indices and then recreate them with any new or changed indices. Now I just emphasized the ALL part for a reason.

Suppose you are a junior developer at some firm in say, St. Louis and you happen to be modifying a table's indices, let's say the F4210 (this is the Sales Order table) which in my PURELY HYPOTHETICAL doesn't get purged and is therefore very large. And so you hit the generate indexes button and wait for the regeneration to be done. WELL THEN... your indices would be wiped out and so what happens with all the HYPOTHETICAL users in Sales Order processing? All of their update processes go to find the most appropriate index to use for their update/add/inquiry and the database operating system can't find any (static indices) because the stupid developer has deleted them all and besides the DB OS is busy trying to rebuild them over the next few hours (because the file is sooooooo large and he did it in the peak of the workday). So anyway the DB OS can't find an optimal index to work with so it starts to create on on the fly (I understand this was the basis of logical files on the AS400). So the users start seeing the wait 'hourglass' and so they call it in to the IT and a few of those people go into the Sales Order system to see what the users are seeing, and before you know it the system is spinning off it's wheels trying to rebuild the static indices, and build on-the-fly indices to the point that the whole system bogs down (even the non-Sales Order processing parts). Now luckily in my HYPOTHETICAL situation the junior developer realizes what is causing all the problems and tells the proper IT personnel before they shut down the system and reboot! Everybody lives with a slow system for the next few hours (luckily it was most user's end of day) and then the system was back to normal.

Hypothetically, procedures are then put in place to only generate the large tables' indices after hours and everybody lived happily ever after.

Ben again

 

[BBritain]

So Raj,

I know we got sidetracked on the index issue, but did you solve your 'key update problem'?

Ben again

 

[mudhens]

Just to set the record straight, the poster (who works with me) wants to update a primary key using Table IO in an event rule. The sql statement he wants to execute would look like "update table1 set field1 = 'XYZ' where field1 = 'ABC'". The problem is the table IO wizard only allows you to use an = sign for the where clause or an arrow sign for the set clause when he needs both.

The best suggestions I have seen are the UKID idea and the suggestion to insert the new record and delete the old record in two steps.

Thanks for your help!

Richard

SP19, SQL Server 2000