> > > 2. correctly coded but due to duplicate row, no insertion possible (expect
> > > 0E0)
> > ... Mysql appears to treat the duplicate primary key case as
> > an error instead of as a correct statement affecting 0 rows.
> > (Doing it from within the mysql utility returns "ERROR 1062", which
> > would confirm that view.)
> Oracle behaves in the same way. I figured this was the standard behavior.
> ORA-00001: unique constraint (USER.CONSTRAINT_NAME) violated
Yes, duplicate primary key is an ERROR in relational databases, at least any
robust business-quality ones. If you find one that it isn't, please warn us!
Were you thinking that since Relational databases are based on set theory, you
should be able to add the same thing 3 time and find it contains one of 'em?
This may be viewed as a "reality wins over theory" issue -- adding a Unique
item that is already there means it's not unique.
Most Business applications test for non-existance before adding. In a
multiuser/multithreaded environment, one must either hold a transaction open
after the check, or trap the duplicate key error and recover.
Bill
