[NCLUG] Annoying post-meeting SQL curiosities

[Sean Reifschneider]

Aaron D. Johnson wrote:
> at a time.  If an UPDATE is truly an all-rows-are-updated or
> no-rows-are-updated operation, should not the new values be checked
> for uniqueness against all the new values of all of the modified rows?

I hear what you're saying, but I side with Bob here that atomic means that
either it completes or it remains unmodified -- atomicity really doesn't
imply that everything is changed before error conditions are checked.  Just
that if errors are found, any changes are rolled back.

I suspect, if you file these bugs, you'll get references to the chapter and
verse in the ANSI SQL spec supporting one or both of these behaviors.  My
bet is on both are allowed.  ;-/

The right way to do it in the database engine may be to do all the
updates, ignoring the changing index, then build the new index.  However,
for best performance you really only want to do this if you know that all
the rows are going to change (easily known for your example code, possibly
not so easily known for your real world code).  Obviously, you don't want
to do this if only one row of a bazillion row table is updated.  Then if
the new index(es) builds correctly, get rid of the old one -- otherwise get
rid of the new one (and associated data changes).

The down side of doing that is that you can't stop the update early on if
you find that the uniqueness constraint really is done.

I'm not sure I understand where Bob gets off calling it a primary key
though.  I usually create tables with an autoincrement field listed as
"PRIMARY", but I will often have one or more unique constraints on other
fields or combinations of fields.  For example, the Hacking Society meeting
database has a unique constraint on the combination of chapter name and
meeting name, so multiple chapters can have the same meeting name, but a
single chapter cannot.

Perhaps this is just a terminology thing, that unique and primary are used
interchangeably in the database world, but logically if I think about a
single table having multiple primary keys my head says "No nono!"  :-)

Sean
-- 
Sean Reifschneider, Member of Technical Staff <jafo at tummy.com>
tummy.com, ltd. - Linux Consulting since 1995: Ask me about High Availability

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 251 bytes
Desc: OpenPGP digital signature
URL: <http://lists.nclug.org/pipermail/nclug/attachments/20081211/707e5f25/attachment.pgp>