Hi,
we have a Fk pointing to a parent table/column - and now sadly enough
that primary key value will change.
What would be a smart way to implement this change to these child
tables ?

Disabling FK's and a lot of update clauses or .... ??

BR,
timo

Op 26-2-2010 9:36, eh schreef:
If it changes, it's not a primary key...
Anyway, if there are no other unique keys on the master table, do not
change the key, but copy the record with a new pk, restore child
connections by updating the foreign keys and delete or archive the
original master record. Note that while performing these actions,
queries may give wrong results (sum, count, etc).

Shakespeare

On Feb 26, 4:56*am, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
Shakespeare has given the correct sequence for the process where it is
necessary to update a PK to a new value. I will agree that as a
general rule a PK should not change; however, in the real world it is
sometimes necessary to change a PK which is why the ANSI standard
requires that PK in fact be updatable. Mergers and acquisition
activity often leads to the need to re-key information where the two
merged parties had overlap in vendors or customers.

But if you find it necessary to update a PK value which in turn
requires updating related data on a regular basis then you do need to
re-examine the choice of PK.

HTH -- Mark D Powell --

Op 26-2-2010 15:11, Mark D Powell schreef:
That is why some standards propagate technical keys, but they can make
building applications a crime, and even then mergers etc will possibly
create problems, since most technical keys are numbers....

Shakespeare

On Feb 26, 6:11*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
I had a situation where people were preparing reports for auditors,
and noticed some invoices had dropped off reports. Eventually I
discovered some customers had moved, and changed their addresses.
This system allows multiple invoicing and delivery addresses, so there
is by convention addresses with DEFAULT as part of an address code
compound key. The actual address is not part of the key, because
obviously, businesses move all the time. So doesn't it make perfect
sense to keep the DEFAULT row and change the address? Well, not if
you are running tax reports for auditors that look at the state in the
address row. Obviously, the state (or should I say, address state
state) should have been kept at the time of invoice. But golly gosh
gee, wouldn't that be violating normal form as you replicate these
foreign keyed rows? Perhaps the design problem was in creating
something called default and expecting users to realize they need a
new address code that isn't called default and change the code to not
default to default... I actually had to do another involved project to
keep the invoice as printed just to be able to reprint them, things
like "this part backordered" are dynamic. Problem with classic
relational theory, it lacks time dynamism, it's not always just
another attribute as you discover unelocuted requirements.

jg
--
@home.com is bogus.
"After tweeting new paper, 193 downloads in 20 days. After blogging
it, 209 downloads in 20 hours." - Cary Millsap (tweeted)

On Fri, 26 Feb 2010 00:36:45 -0800, eh wrote:

Redesigning the application would be the best choice, if you ask me.
Public execution of the original designer which includes quartering
should also be considered.



--
http://mgogala.byethost5.com

"eh" <timo.talja.email (AT) gmail (DOT) com> wrote


As pointed out by Mark Powell, relational theory does
allow primary keys to change; but there is a feeling in the
market place that it's not supposed to happen - and you
don't see it happening in many systems.

In Oracle implementations, you need to create the foreign
key constraint as "deferrable initially immediate". Then your
code goes something like this:

alter constraint {fk constraint} deferred;
update rows in child table to match not-yet-existing primary key;
update key of parent table;
alter constraint {fk constraint} immediate;

If you've made a mistake at this point Oracle raises one of several
errors (but I can't remember all the variations and numbers - the
obvious set of experiments will identify them).

You can examine the error and decide on what action to take -
probably rolling back. It's probably best to do one PK at a time
as this makes it easier to work out what's gone wrong.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

"joel garry" <joel-garry (AT) home (DOT) com> wrote

Not a problem - Chris Date cracked that a few years
ago with a Hugh Darwen and another persons whose
name I forget. Resulted in 6th Normal form, which
collapsed to 5th Normal form in the absence of time-
dependency and time-dependent operators.

I think someone from Oracle had a presentation about
their work in the same area a couple of years ago.

It allows "convenient" (though not necessary efficient)
handling of queries like:

What would the data have looked like if I had queried
it on 10th Feb allowing for the fact that there was an error
on 9th Feb that wasn't corrected until 11th Feb - and I want
to see the correct item, not the error.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

eh <timo.talja.email (AT) gmail (DOT) com> writes:

Alter the FK constraint to deferred. Then, update the parent key with a
follow-on update to the child key and then commit. Since the constraint
is deferred, it won't bark at you during the operation.

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

On Feb 27, 12:51*am, "Jonathan Lewis" <jonat... (AT) jlcomp (DOT) demon.co.uk>
wrote:
Hardly call 2006 "classic." :-)

Yes, this is the type of thing I'm referring to, though it far too
often reflects a more basic change in data definitions. I haven't yet
seen 6nf used in any way except as a custom written fix on the fairly
straightforward types of systems I work on, and only implicitly.
MRP's and ERP's take years to develop, and more years to life cycle in
production. And the time series stuff I've written to plop into those
started before 2006.

It's good that someone big has made a theoretical basis for this, but
then again, kids today seem to think dinosaurs R us.

jg
--
@home.com is bogus.
http://boards.straightdope.com/sdmb/...d.php?t=553635