🚀 go-pugleaf

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)