MySQL unique column string

Question

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 like id), but mc_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)?

score 8 · Accepted Answer · answered Nov 25 '12 at 22:56

8

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;

answered Nov 25 '12 at 22:56

John Woo

258,903
69
498
492

How 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... – Mathlight Nov 25 '12 at 23:02
you can try to alter the table by running this DDL: `ALTER TABLE twMCUserDB ADD UNIQUE tb_uq (mc_userName)` – John Woo Nov 25 '12 at 23:04
That returns this: `[Err] 1170 - BLOB/TEXT column 'mc_userName' used in key specification without a key length` – Mathlight Nov 25 '12 at 23:06
try changing the databatype to `varchar(100)` since it is username – John Woo Nov 25 '12 at 23:08
1

or `varchar(50)` will suffice – John Woo Nov 25 '12 at 23:09
That did work, but are there difrnce betwwen text and varchar? – Mathlight Nov 25 '12 at 23:13
VARCHAR you can set a limit for how many chars it will accept per record, text is (virtually) unlimited *which you don't necessarily want*) – John Woo Nov 25 '12 at 23:15

score 1 · Answer 2 · edited May 23 '17 at 11:46

1

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

edited May 23 '17 at 11:46

Community

1
1

answered Nov 25 '12 at 22:31

w0051977

15,099
32
152
329

I 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 Nov 25 '12 at 22:34
sorry, i meant that first part... Making the mc_userName the primary key instead of the id.... i get the error i sayed above – Mathlight Nov 25 '12 at 22:36
I have made a change to my answer. Can you change the data type of mc_userName to VARCHAR? – w0051977 Nov 25 '12 at 22:39
This link may help you: http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length – w0051977 Nov 25 '12 at 22:40
I 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 Nov 25 '12 at 22:49
Don'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). – w0051977 Nov 25 '12 at 23:09
I 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 Nov 25 '12 at 23:12

MySQL unique column string

2 Answers2