Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    London England
    Posts
    8

    Unanswered: question about Unique

    Hello,

    When defining a field as unique within a table, I understand that you use Unique(fieldname) when creating the table.

    I'm using a BLOB to store a long string, and want these long strings to be unique. If I try to use Unique(type) (where type is a blob) I get the error:

    Error 1170 Blob column 'type' used in key specification without a key length

    Does anybody know how I can make ths Blob unique?

    Cheerz,

    Steve

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index:
    Code:
    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
    -- http://www.mysql.com/doc/en/CREATE_T...E TABLE Syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    London England
    Posts
    8
    I'm sorry, i'm a little confused!

    How do I use the code:
    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

    to make blob_col unique?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am confused about your confusion

    since your table exists already, there's little point in using CREATE TABLE

    you will probably want to try this --

    ALTER TABLE yourtable
    ADD UNIQUE(blob_col(10)));
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    London England
    Posts
    8
    I am building the database at the moment fo a university project so am just dropping and recreating the whole database as it changes - that is why I am using create table.

    I am currently creating the table as:

    CREATE TABLE TOOL (toolNumber INT NOT NULL AUTO_INCREMENT, type BLOB, location VARCHAR(255), returnDate VARCHAR(30), PRIMARY KEY(toolNumber));

    Now I want to change this so that the field type is unique.

    The statments

    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

    ALTER TABLE test
    ADD UNIQUE(blob_col(10));

    return the error:
    ERROR 1061: Duplicate key name 'blob_col'

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    The statments

    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

    ALTER TABLE test
    ADD UNIQUE(blob_col(10));

    return the error:
    ERROR 1061: Duplicate key name 'blob_col'
    that should not really surprise you

    you create an INDEX on the blob column, then attempt to add a UNIQUE index as well

    may i suggest you define only one index on the column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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