Page 1 of 2

Trouble with UNIQUE in MySQL

Posted: Mon Jun 07, 2004 5:34 pm
by pickle
Hi All,

I'm trying to set up a web application that will (among other things), allow the user to set if a column is UNIQUE or not. I've auto generated the query to look as follows:

Code: Select all

ALTER TABLE 
     computers 
CHANGE 
     mac mac text UNIQUE
However, when I do that, I get the error:

Code: Select all

ERROR 1170: BLOB column 'mac' used in key specification without a key length
What does this mean? How do I go about fixing this? Thanks.


Edit:::: I've been helped with this, but I've got another question about UNIQUE, seen below

Posted: Mon Jun 07, 2004 5:56 pm
by markl999
In order to index a TEXT or BLOB type you must specify an index length and mysql can't index past the first 255 chars so that means your text field cannot be more than 255 chars if you want to index it/may it unique.

Posted: Mon Jun 07, 2004 6:43 pm
by pickle
Ok, that's kind of what i figured, but how would I go about putting an index length in there? Something like:

Code: Select all

ALTER TABLE
    computers
CHANGE
    mac mac text UNIQUE(100)
??

I tried that but I got another error (can't remember which one, but I'm pretty sure my syntax is wrong anyway).

Thanks

Posted: Mon Jun 07, 2004 6:55 pm
by markl999
Well, are you planning to make the text field only 100 chars wide or do you mean only index the first 100 chars?
In the first case you might as well make it a varchar (100) unique, in the latter case you can't. What i basically meant in my first post is that a text/blob field can't be unique unless the length of it is 255 (length of the column, not just the index).

Posted: Tue Jun 08, 2004 9:40 am
by pickle
Hmm, ok. Thanks.

Posted: Tue Jun 08, 2004 11:06 am
by pickle
New question: How do I remove the UNIQUE quality. My user's will also have the ability to stop a field from being unique. However, doing this statement:

Code: Select all

ALTER TABLE 
     computers 
CHANGE 
     test_field test_field text
Gives me the same error, which leads me to believe I need to remove the UNIQUE property (which sets the column as a "MUL" key). I tried:

Code: Select all

ALTER TABLE 
     computers 
CHANGE 
     test_field test_field text NOT UNIQUE
but that's bad syntax.

Any thoughts? I'm sure it's drop dead simple.

Posted: Tue Jun 08, 2004 1:27 pm
by feyd
quickly slapping a new table in my test db:

ALTER TABLE `test` DROP INDEX `id`

Posted: Tue Jun 08, 2004 2:41 pm
by pickle
Nope, that didn't work. It said it affected all the rows in the table, but when I check the structure, there's no change - the key value for the field is still "MUL". Thanks though.

Posted: Tue Jun 08, 2004 2:49 pm
by Weirdan
I checked feyd's solution and it works for me.
What 'SHOW INDEXES FROM `your_table`' statement shows?

Posted: Tue Jun 08, 2004 3:48 pm
by pickle
I get this:

Code: Select all

+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| computers |          0 | PRIMARY       |            1 | id          | A         |         202 |     NULL | NULL   |      | BTREE      |         |
| computers |          0 | test_field_2  |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_3  |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_4  |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_5  |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_6  |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_7  |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_8  |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_9  |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_10 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_11 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_12 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_13 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_14 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_15 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_16 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_17 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_18 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_19 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_20 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_21 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_22 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_23 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_24 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_25 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_26 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_27 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_28 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_29 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| computers |          0 | test_field_30 |            1 | test_field  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
which strikes me as a little troublesome. Basically what I want to do is allow the user to choose and alternate whether a column is unique or not. Am I going about this the right way?

Posted: Tue Jun 08, 2004 4:10 pm
by Weirdan
you have created 30 unique indices on that column :lol:
You need to drop them all now ;)

Posted: Tue Jun 08, 2004 4:24 pm
by pickle
Ya, I thought so, which made me question my overall technique. Currently, what I'm doing is, whenever a user chooses to make a column unique, I call

Code: Select all

ALTER
TABLE
   the_table
CHANGE
   the_column the_column varchar(255) UNIQUE
so.. to undo that change, I need to make 2 queries:

Code: Select all

ALTER
TABLE
   the_table
DROP INDEX
   the_field_that_is_unique
... then just change the field back to whatever type I want. Is this correct? Thanks for the help so far guys!!

Posted: Tue Jun 08, 2004 5:59 pm
by feyd
what about, instead of setting and removing unique index, just track which field they want unique somewhere (in their preferences or global preferences) and do a quick select query prior to inserting a row?

Posted: Wed Jun 09, 2004 9:50 am
by pickle
Ya, I thought of that, but I'm trying to make this system as close to a simple front-end to a database as possible (for no particular reason, I just want to see how close I can get). I want to rely on the database as much as possible for data integrity. Thanks though.

Posted: Wed Jun 09, 2004 7:50 pm
by Weirdan
Your app could check if the column already had unique index on it and refuse to create the new one.