I even had trouble getting the auto_increment to work... docnumber was fussy and wanted to be the first in order in order for the auto_increment to actually perform the increment. Having come from oracle this seems flakey.
If anyone can straighten me out I would be greatful. I want to use this beast.
Your primary key needs adjusting because you have
defined it to be a compound key composed of one
field (docnumber, which is ALWAYS unique) and
another field, document_name, which doesn't have to be unique because docnumber always is.
If you want document_name to be unique then change
primary key to just use this one field (document_name).
'PRIMARY KEY' means take those two fields and combine
them to make a unique key, but since docnumber is
always unique MySQL never even has to consider
document_name. In this case, the only benefit you're getting by
making the primary key two fields instead of one field is an
index is automatically created that makes searching on
docnumber and document_name faster, but that's it.
You're losing more than you're getting, however, because
the current primary key definition doesn't acurately
define a unique record, and thus duplicate document_name
records are allowed to be inserted.
'PRIMARY KEY' does not mean "each of the primary key fields
must be unique." It means all of the fields taken together
must be unique. Subtle, but different.