MySQL unique column string

mysql sql primary-key unique unique-key
Mathlight picture Mathlight · Nov 25, 2012 · Viewed 12.2k times · Source

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)?

Answer

John Woo picture John Woo · Nov 25, 2012

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;

Related questions

Can I use VARCHAR as the PRIMARY KEY?

I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a VARCHAR. My rationale is that, each coupon will have a unique code, and the only commands …

Read More
How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More
difference between primary key and unique key

I'm using mysql database. I have a confusion between primary key and unique key. Please help me where should I create primary and unique key. I mean in which situation we create unique key or primary key .

Read More