🚀 go-pugleaf

David Domingo wrote:

>   How can I replicate any changes of primary key? It is possible?

	Primary keys should never change.

	-Craig

P.S.: Yes, it would be possible if you set up an alternate key for
replication.  But not advisable.

	-Craig

-- 
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
  Delphi/InterBase Weblog : http://delphi.weblogs.com
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt 
  your DBs!  Open Edition users, get 6.0.1.6 from http://mers.com

"Craig Stuntz [TeamB]" <cstuntz@nospam.please [a.k.a. vertexsoftware.com]>
escribi� en el mensaje news:4034e8dc$1@newsgroups.borland.com...
> David Domingo wrote:
>
> >   How can I replicate any changes of primary key? It is possible?
>
> Primary keys should never change.
>


Then, what is the purpose of foreign keys with update cascade? ;)
Could be available this one feature (query update replication with oldkey
values) for future versions of IBRepl?


>
> -- 
> Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
>   Delphi/InterBase Weblog : http://delphi.weblogs.com
> IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt
>   your DBs!  Open Edition users, get 6.0.1.6 from http://mers.com

David Domingo wrote:

> Then, what is the purpose of foreign keys with update cascade? ;)

	This changes the table that *references* the PK, not the PK value
itself.

	Repeat: Primary key values should never change.  If you ever feel the
need to change them then you have put business data in your PK, which
is a bad idea since PKs should never change.  :)

	In my ever-so-biased opinion,

	-Craig

-- 
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
  Delphi/InterBase Weblog : http://delphi.weblogs.com
Everything You Need to Know About InterBase Character Sets:
  http://delphi.weblogs.com/stories/storyReader$306

On Fri, 20 Feb 2004 11:12:14 +0100, "David Domingo"
<david.domingo@starseguridad.com> wrote:

>Then, what is the purpose of foreign keys with update cascade? ;)

The purpose is to allow you to use an unstable attribute for the
primary key. This does not make it a good idea. As you have already
discovered, an unstable primary key makes replication very difficult.
Unstable primary keys also cause concurrency problems. Suppose you
have a table with an unstable key and this table has a one-to-many
relationship with a table that has the cascade update option for its
foreign key. One user changes the primary key value in the master
table. Another user has changed one of the detail records and has not
committed yet. Since one of the detail records is locked the primary
key change cannot be cascaded. Depending on the transaction options of
the user trying to change the primary key he/she will either get an
error or hang until the user changing the detail record either commits
or rolls back.

Stability is second only to uniqueness in importance when choosing a
primary key. If there is no attribute or combination of attributes
that is both unique and stable use a surrogate primary key. You will
save yourself a lot of headaches.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

"Bill Todd (TeamB)" <no@no.com> escribi� en el mensaje
news:mcic30d6avuml13ilsuokrerfncilfpjtu@4ax.com...
> On Fri, 20 Feb 2004 11:12:14 +0100, "David Domingo"
> <david.domingo@starseguridad.com> wrote:
>
> >Then, what is the purpose of foreign keys with update cascade? ;)
>
> The purpose is to allow you to use an unstable attribute for the
> primary key. This does not make it a good idea. As you have already
> discovered, an unstable primary key makes replication very difficult.
> Unstable primary keys also cause concurrency problems. Suppose you
> have a table with an unstable key and this table has a one-to-many
> relationship with a table that has the cascade update option for its
> foreign key. One user changes the primary key value in the master
> table. Another user has changed one of the detail records and has not
> committed yet. Since one of the detail records is locked the primary
> key change cannot be cascaded. Depending on the transaction options of
> the user trying to change the primary key he/she will either get an
> error or hang until the user changing the detail record either commits
> or rolls back.
>
> Stability is second only to uniqueness in importance when choosing a
> primary key. If there is no attribute or combination of attributes
> that is both unique and stable use a surrogate primary key. You will
> save yourself a lot of headaches.
>




Ok thanks, but this question is one question with irony. Of course I know
what is the purpose of update cascade.

Sorry but I can't be in agreement with your exposition.

The only one change in current version of IBRepl is in UPDATE:

    update TABLE set PRIMARYKEY=NEWKEYVALUES,..... where
PRIMARYKEY=OLDKEYVALUES

But if you cant do this changes dont worry. When own replicator engine is
finished we will sent you a copy for your evaluation. That's ok?

THANKS and regards from Spain.


> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)

David Domingo a �crit :
> The only one change in current version of IBRepl is in UPDATE:
> 
>     update TABLE set PRIMARYKEY=NEWKEYVALUES,..... where
> PRIMARYKEY=OLDKEYVALUES
> 

 From my own experience, I would say that it would be much more complex 
than that to make IBReplicator handle changing PK values.

I handle this in my own replicator, but I have a completely different 
approach, which makes it easy. If you log every change, indicating 
whether it is an INSERT, an UPDATE or a DELETE, and then you replicate 
this change in the same way to the other side (if I understand 
correctly, this is how IBReplicator works), then it will be very 
difficult to handle PK changes. Suppose in DB 1, a user inserts a record 
with PK=34, and then updates it to 35 before replicating. When the 
replicator starts, it will see two changes : one insert, and one update. 
So it will try to perform the insert, but it won't succeed, since the 
record referenced by the line in the replication log corresponding to 
the insert, no longer has any corresponding record. Then, the update 
also doesn't work, obviously, since the insert didn't.

Jonathan Neve.

> I handle this in my own replicator, but I have a completely different
> approach, which makes it easy. If you log every change, indicating
> whether it is an INSERT, an UPDATE or a DELETE, and then you replicate
> this change in the same way to the other side (if I understand
> correctly, this is how IBReplicator works), then it will be very
> difficult to handle PK changes. Suppose in DB 1, a user inserts a record
> with PK=34, and then updates it to 35 before replicating. When the
> replicator starts, it will see two changes : one insert, and one update.
> So it will try to perform the insert, but it won't succeed, since the
> record referenced by the line in the replication log corresponding to
> the insert, no longer has any corresponding record. Then, the update
> also doesn't work, obviously, since the insert didn't.

Er no. Only committed changes get replicated.
So this would really be insert PK=34 commit - Write replication insert to
replication log.
Then the insert takes place on the target with PK=34 data=blah

Now if you update the primary key, and make it 35, commit,  an update would
fail on the
target because replication expects to find the same primary key in the target
database
as the source i.e. update 35 which doesn't exist. Changing primary keys in
distributed databases (which includes replicated ones) is not the brightest
move.

Paul

Paul Beach a �crit :
> Er no. Only committed changes get replicated.
> So this would really be insert PK=34 commit - Write replication insert to
> replication log.
> Then the insert takes place on the target with PK=34 data=blah
> 
> Now if you update the primary key, and make it 35, commit,  an update would
> fail on the
> target because replication expects to find the same primary key in the target
> database
> as the source i.e. update 35 which doesn't exist. Changing primary keys in
> distributed databases (which includes replicated ones) is not the brightest
> move.

Yes. What I meant was : Insert -> Commit -> Update -> Commit -> 
Replicate. This, if I understand correctly, would indeed fail, right?

Jonathan Neve.