MySQL unique column string
Solution 1
just add UNIQUE
`mc_userName` text NOT NULL UNIQUE,
or
CREATE TABLE `twMCUserDB` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mc_userName` text NOT NULL,
`mc_userPass` text NOT NULL,
`tw_userName` text NOT NULL,
`tw_userPass` text NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT tb_uq UNIQUE (mc_userName)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Solution 2
Why don't you make 'mc_userName ' the Primary Key instead of id? You will have to change the data type of mc_userName to VARCHAR as described in this post: MySQL error: key specification without a key length. Alternatively, create a composite primary key containing id and username.
If you can't do this because of the design of the database then use the EXISTS keyword in a query. Have a look here for more information: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html
Mathlight
Updated on June 04, 2022Comments
-
Mathlight about 2 years
Currently I have this table in my database:
CREATE TABLE `twMCUserDB` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mc_userName` text NOT NULL, `mc_userPass` text NOT NULL, `tw_userName` text NOT NULL, `tw_userPass` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
I want the column
mc_userName
to be unique too (just likeid
), butmc_userName
must be a string.I tried to make it also a primary key, but that didn’t work.
When I add data to the table, do I first have to check whether the
mc_userName
already exists? Or is there any built-in function in MySQL that I can use (in the insert query, or somewhere else)? -
Mathlight over 11 yearsI can't do that, because i get the error
MySQL Error 1170: BLOB/TEXT Column Used in Key Specification Without a Key Length
... And i will take an look at that link -
Mathlight over 11 yearssorry, i meant that first part... Making the mc_userName the primary key instead of the id.... i get the error i sayed above
-
w0051977 over 11 yearsI have made a change to my answer. Can you change the data type of mc_userName to VARCHAR?
-
w0051977 over 11 yearsThis link may help you: stackoverflow.com/questions/1827063/…
-
Mathlight over 11 yearsI tried to change it to var char, making the id not primairy anymore, but only the mc_userName, but then i get this error: `ERROR 1167: The used storage engine can't index column 'mc_userName'
-
Mathlight over 11 yearsHow can i add that UNIQUE to the colum than? i'm using Navicat as database editor, and phpmyadmin, but can't find something that can handle this...
-
John Woo over 11 yearsyou can try to alter the table by running this DDL:
ALTER TABLE twMCUserDB ADD UNIQUE tb_uq (mc_userName)
-
Mathlight over 11 yearsThat returns this:
[Err] 1170 - BLOB/TEXT column 'mc_userName' used in key specification without a key length
-
John Woo over 11 yearstry changing the databatype to
varchar(100)
since it is username -
w0051977 over 11 yearsDon't forget that changing from a Text datatype to a VARCHAR may cause data to be truncated e.g. 'Hello' would become 'Hell' with VARCHAR(4).
-
John Woo over 11 yearsor
varchar(50)
will suffice -
Mathlight over 11 yearsI think exists is indeed the best option. I hoped that there was some kind of simple way to do, but this is fine to...
-
Mathlight over 11 yearsThat did work, but are there difrnce betwwen text and varchar?
-
John Woo over 11 yearsVARCHAR you can set a limit for how many chars it will accept per record, text is (virtually) unlimited which you don't necessarily want)