Create MySQL-Index to prevent doublepost?

Question

I have a table comments with the (simplified) rows id, article_id, user_id and text.

Now I had an idea. Can I somehow create an unique index over article_id, user_id and text to prevent doubleposting directly from the database design?

How would I do that since text is from type TEXT and refuses to be indexed?

You could eliminate the auto-increment `id` field (I'm assuming its AI), and make `article_id`, `user_id` and `text` a compound primary key. — Luke Shaheen, Mar 29 '13 at 03:20
When I try to `ALTER TABLE comments ADD PRIMARY KEY ( article_id , user_id , text )` I get `#1170 - BLOB/TEXT column 'text' used in key specification without a key length` — user2015253, Mar 29 '13 at 03:24
If Brian or I answered your question, please mark the question as answered. If not, what are you still having trouble with? — Luke Shaheen, Mar 29 '13 at 13:45

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

1

you mean to prevent accidental double click on 'save' button? Or prevent posting the same exact text say a week later? If it is the first one, may be just disable your button on first click. If second, you should probably run a hash function on your text field to see if a match already exists for that user on that article. Or add an extra field which is a hash function of your text field and create your unique composite key using that. Like here

edited May 23 '17 at 12:11

Community

1
1

answered Mar 29 '13 at 03:23

Brian

1,337
5
17
34

This is mainly to prevent double posting. Especially when you post something and then use the "BACK" function of your browser, it will send the POST data again. Any idea to prevent this other than by database design? – user2015253 Mar 29 '13 at 03:29
Your idea to prevent it through db design is better. Will prevent duplicate posting forever, not just due to 'back'ing. – Brian Mar 29 '13 at 03:34

score 1 · Answer 2 · edited May 23 '17 at 12:22

To create an INDEX on a TEXT field, you need to specify the index length, like the error is hinting at. This is will only index part of your text field, so you may want to consider using a different field as part of the index, like maybe a DATE field - article names are unique by day then, or something like that.

INDEX UniqueArticle (article_id, user_id, text(100));

For MyISAM tables

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

For InnODB tables

Index key prefixes can be up to 767 bytes. See [Section 12.1.8, “CREATE INDEX Syntax”][1].

See: http://dev.mysql.com/doc/refman/5.6/en/create-table.html

Also see: Problem in using TEXT field in mysql Table

Create MySQL-Index to prevent doublepost?

2 Answers2