Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Mon Jun 07, 2004 5:34 pm
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.
markl999
DevNet Resident
Posts: 1972 Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)
Post
by markl999 » Mon Jun 07, 2004 5:56 pm
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.
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Mon Jun 07, 2004 6:43 pm
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.
markl999
DevNet Resident
Posts: 1972 Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)
Post
by markl999 » Mon Jun 07, 2004 6:55 pm
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).
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Tue Jun 08, 2004 9:40 am
Hmm, ok. Thanks.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Tue Jun 08, 2004 11:06 am
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Tue Jun 08, 2004 1:27 pm
quickly slapping a new table in my test db:
ALTER TABLE `test` DROP INDEX `id`
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Tue Jun 08, 2004 2:41 pm
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.
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Tue Jun 08, 2004 2:49 pm
I checked feyd's solution and it works for me.
What 'SHOW INDEXES FROM `your_table`' statement shows?
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Tue Jun 08, 2004 3:48 pm
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.
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Tue Jun 08, 2004 4:10 pm
you have created 30 unique indices on that column
You need to drop them all now
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Tue Jun 08, 2004 4:24 pm
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Tue Jun 08, 2004 5:59 pm
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?
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Wed Jun 09, 2004 9:50 am
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.
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Wed Jun 09, 2004 7:50 pm
Your app could check if the column already had unique index on it and refuse to create the new one.