Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: MySQL 4.0.1.2 Primary Keys do not work.

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •