Ann W. Harrison wrote:

> Lester Caine wrote:
>
>> Having now seen some statements about 'REPLACE' I think that we could
>> make an argument that the MySQL process is correct in that your new
>> record SHOULD replace the existing one and so perhaps the discussion
>> there would be worth exploring as a DIFFERENT statement to the one
>> currently proposed - with Jim's input on why the MySQL version breaks down.
>
> However appropriate REPLACE is to headlamps or lug nuts, it's lousy in
> databases. If you change a value unrelated to a unique or primary key
> and there are referential constraints dependent on the unchanged key,
> the referencing records should change or disappear. Nor should the
> existence of referencing records cause the statement to fail - at the
> end of the statement the constraint is valid.

As I went on to say 'DELETE AND INSERT' could well have a place when you
need to ACTUALLY replace the existing record rather than simply
UPDATEing it. In that case I would expect to strip all linked records so
that a new version can be built - otherwise I would be using UPDATE OR
INSERT. REPLACE is the correct shorthand for 'DELETE AND INSERT' and
simply wrong for 'UPDATE OR INSERT' ;)

--
Lester Caine - G8HFL
-----------------------------
L.S.Caine Electronic Services - http://home.lsces.co.uk
Model Engineers Digital Workshop -
http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php