[SOLVED] Trouble with UNIQUE in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Trouble with UNIQUE in MySQL

Post 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
Last edited by pickle on Tue Jun 08, 2004 11:08 am, edited 2 times in total.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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).
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Hmm, ok. Thanks.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

quickly slapping a new table in my test db:

ALTER TABLE `test` DROP INDEX `id`
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

I checked feyd's solution and it works for me.
What 'SHOW INDEXES FROM `your_table`' statement shows?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

you have created 30 unique indices on that column :lol:
You need to drop them all now ;)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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!!
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Your app could check if the column already had unique index on it and refuse to create the new one.
Post Reply