How to make a mysql field unique?

Question

Is there a way to make an existing txt field unique (do not accept duplicated values)?

Field: post_title
Type: text
Collation: utf8_unicode_ci
Null: Sim
Default: NULL

What gonna happens if someone tries to insert a post with an existing title?

Could that affect some functionality of my site?

Structure

CREATE TABLE IF NOT EXISTS `hotaru_posts` (
  `post_id` int(20) NOT NULL AUTO_INCREMENT,
  `post_archived` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `post_updatedts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `post_author` int(20) NOT NULL DEFAULT '0',
  `post_date` timestamp NULL DEFAULT NULL,
  `post_pub_date` timestamp NULL DEFAULT NULL,
  `post_status` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'processing',
  `post_type` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_category` int(20) NOT NULL DEFAULT '1',
  `post_tags` text COLLATE utf8_unicode_ci,
  `post_title` text COLLATE utf8_unicode_ci,
  `post_orig_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_domain` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_content` text COLLATE utf8_unicode_ci,
  `post_votes_up` smallint(11) NOT NULL DEFAULT '0',
  `post_votes_down` smallint(11) NOT NULL DEFAULT '0',
  `post_comments` enum('open','closed') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'open',
  `post_media` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text',
  `post_img` text COLLATE utf8_unicode_ci NOT NULL,
  `post_subscribe` tinyint(1) NOT NULL DEFAULT '0',
  `post_updateby` int(20) NOT NULL DEFAULT '0',
  `post_views` int(20) NOT NULL DEFAULT '0',
  `post_last_viewer_ip` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '111.111.111.111',
  PRIMARY KEY (`post_id`),
  KEY `post_archived` (`post_archived`),
  KEY `post_status` (`post_status`),
  KEY `post_type` (`post_type`),
  FULLTEXT KEY `post_title` (`post_title`,`post_domain`,`post_url`,`post_content`,`post_tags`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Story Posts' AUTO_INCREMENT=38275 ;

possible duplicate of [MySQL - Make a Field Unique](http://stackoverflow.com/questions/5038040/mysql-make-a-field-unique) — mechanical_meat, Mar 02 '12 at 03:33
that didnt help, all queries give same ERROR #1170 - BLOB/TEXT column 'post_title' used in key specification without a key length — Lucas Matos, Mar 02 '12 at 03:56
Don't make the Post title unique it is not required, post id is unique, so when someone votes on any post, you will update the row based on the post_id so it will not create a problem — Naveen Kumar, Mar 02 '12 at 05:19

score 4 · Answer 1 · answered Mar 02 '12 at 07:10

here error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happen when there is a field/column type of TEXT or BLOB or those belongs to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make as primary key or index. With full BLOB or TEXT without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when using BLOB or TEXT types as index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support limit on TEXT or BLOB. TEXT(88) simply won’t work.

So the solution is remove TEXT and set to VARCHAR datatype with lenght of 255.(BY default lenght).

`post_title` varchar(255) COLLATE utf8_unicode_ci UNIQUE KEY

If you have created table then write this. " alter table hotaru_posts change post_title post_title varchar(255) COLLATE utf8_unicode_ci UNIQUE KEY; " — Dhruvisha, Mar 03 '12 at 10:37

score 0 · Answer 2 · answered Mar 02 '12 at 03:39

0

Try This:

ALTER TABLE tableName   
  ADD  UNIQUE INDEX `post_title_Index` (`post_title`);

answered Mar 02 '12 at 03:39

John Woo

238,432
61
456
464

ERROR #1170 - BLOB/TEXT column 'post_title' used in key specification without a key length – Lucas Matos Mar 02 '12 at 03:53
1

can you post the schema so we can reproduce the problem? – John Woo Mar 02 '12 at 03:55
sorry but, what do you mean? Wich schema you talking about? – Lucas Matos Mar 02 '12 at 03:58
Schema means the structure of your table. – John Woo Mar 02 '12 at 04:00
Check first post pls, added the schema. – Lucas Matos Mar 02 '12 at 04:13

score 0 · Answer 3 · answered Mar 02 '12 at 07:07

0

I think you need to add a length to the post_title field. Give it some number and that should resolve the error.

ALTER TABLE `hotaru_posts` 
  ADD  UNIQUE INDEX `post_title_Index` (`post_title`,`value`(255));

Give that a shot.

answered Mar 02 '12 at 07:07

FajitaNachos

970
8
20

same ERROR #1170 - BLOB/TEXT column 'post_title' used in key specification without a key length – Lucas Matos Mar 02 '12 at 16:02

How to make a mysql field unique?

3 Answers3

Linked