Re: A problem in SQL (a real challenge)

Gene> What if it is not abuse?  I write/support a client billing system
Gene> where it is possible to have more than one transaction with what might
Gene> be considered the primary key.  These are generally dealt with in a
Gene> group.  It is usually irrelevant to my boss which order they are
Gene> listed in, but a transaction number can be entered to force a
Gene> particular order.  Whether the group has one transaction in it or more
Gene> is irrelevant: all of the transactions will be consolidated for the
Gene> invoice.  

Seems like a recipe for disaster to me: how can you ever know that two $100 items are real separate items, rather than an error? Having a proper key (and enforcing it using a constraint) helps a bit against this. Of course if won't help against entering it erroneously, but that's another matter. And with the key at least it's easy to get of the duplicates in a portable way.

Secondly, maybe you don't need or want that key right now, but needs have a habit of changing; so it's usually pays off to stay on the safe side (and anyway, what's there against having an extra key field? Most of your database will or should be set up fairly generously, if you expect any growth. Waisting a few bytes per row for a key should be neglible). As with trying to remove duplicate entries, it is tricky to assign a primary key 'later on' when the need arises.

                                                                      Philip

-- 
/dev/brain:  character special (53/0)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk | European Bioinformatics Institute,rm A2-24


+44 (0)1223 49 4639                 | Wellcome Trust Genome Campus, Hinxton


+44 (0)1223 49 4468 (fax)           | Cambridgeshire CB10 1SD,  GREAT BRITAIN

PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53