Can anyone tell me why my table continues to accept instances of the same document_name ?

My unique keys are as if they are not even there ...?

Help Please

DO I need to specify ISAM something ?

Here it is...

email appreciated...

drop table document_t;

create table document_t (
email_address VARCHAR (128) default '' NOT NULL,
document_name VARCHAR (128) default '' NOT NULL,
file_name VARCHAR (255),
file_extension VARCHAR (255),
num_bytes BIGINT,
mime_content_type VARCHAR (255),
description VARCHAR (255),
binary_blob LONGBLOB,
created BIGINT,
docnumber BIGINT NOT NULL AUTO_INCREMENT,
primary key (docnumber,document_name),
foreign key (email_address) references user_t
);


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.


__________________
- cheers

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.

-lv